Anyone any good at SQL?

andyc209 · Post by **andyc209** » Wed Feb 12, 2003 3:53 pm

Writing a BIG query in SQL but got stuck....banging head on brick wall .....

anyone????

paul_y3k · Post by **paul_y3k** » Wed Feb 12, 2003 4:07 pm

bang it over ... I'll stick my ocp hat on

andyc209 · Post by **andyc209** » Wed Feb 12, 2003 4:47 pm

I have an SQL database with a table with Fax Logs in. Each row as a number which corresponds to a faxed order and a date and time it was sent.

If the fax has had to be resent I get a new record with a duplicate faxed order number as with the original record and a new date/time.

How can I query this so thatI return only the newest fax record and not the older one in my table of records.

SO far i have

SELECT dbo.faxlog.Col001 AS FaxDate, dbo.faxlog.Col002 AS FaxTime, dbo.faxlog.Col003 AS FaxUser, dbo.faxlog.Col004 AS FaxSPOID,
dbo.faxlog.Col005 AS FaxOrganisation, dbo.faxlog.Col007 AS FaxNumber, dbo.faxlog.Col009 AS FaxPages, dbo.faxlog.Col010 AS FaxDuration,
dbo.faxlog.Col012 AS FaxFrom, dbo.ZFAXerror.Description AS FaxErrorDescription, dbo.faxlog.Col008 AS FaxError
FROM dbo.ZFAXerror INNER JOIN
dbo.faxlog ON dbo.ZFAXerror.Hex = dbo.faxlog.Col008
WHERE (dbo.faxlog.Col003 = 'MPP')

and i need it to show all the fax logs but only the newest fax where FaxSOPID might be the same.

cheers

:rolleyes: :rolleyes:

andyc209 · Post by **andyc209** » Wed Feb 12, 2003 4:48 pm

PS sorry lyndon, i know this is a diversion but there was not much happening on the site and i really needed help!

MattB · Post by **MattB** » Wed Feb 12, 2003 5:52 pm

You need to select the MAX date in the SELECT, then GROUP BY the id.

Untested, but shuold work:

SELECT MAX(dbo.faxlog.Col001) AS FaxDate, dbo.faxlog.Col002 AS FaxTime, dbo.faxlog.Col003 AS FaxUser, dbo.faxlog.Col004 AS FaxSPOID,
dbo.faxlog.Col005 AS FaxOrganisation, dbo.faxlog.Col007 AS FaxNumber, dbo.faxlog.Col009 AS FaxPages, dbo.faxlog.Col010 AS FaxDuration,
dbo.faxlog.Col012 AS FaxFrom, dbo.ZFAXerror.Description AS FaxErrorDescription, dbo.faxlog.Col008 AS FaxError
FROM dbo.ZFAXerror INNER JOIN
dbo.faxlog ON dbo.ZFAXerror.Hex = dbo.faxlog.Col008
WHERE (dbo.faxlog.Col003 = 'MPP') GROUP BY FaxSPOID

jim · Post by **jim** » Wed Feb 12, 2003 6:46 pm

I think you'll get either, as the distinct used in that way will only look at FaxSOPID.

Who'd have thought i'd end up having an SQL converstion on this site?!? Or even that theres so many people here who know it. Maybe we should have an sql area so i know where to come next time i am stuck.

Erm · Post by **Erm** » Wed Feb 12, 2003 6:48 pm

MattB · Post by **MattB** » Wed Feb 12, 2003 7:30 pm

I think you'll get either, as the distinct used in that way will only look at FaxSOPID.

Who'd have thought i'd end up having an SQL converstion on this site?!? Or even that theres so many people here who know it. Maybe we should have an sql area so i know where to come next time i am stuck.

Beware though - if you have a lot of data, DISTINCT in SQL Server is painfully slow.

Maybe we should get some car stickers made up... "SELECT owner FROM 206cc WHERE geek='yes'"

Or something...

paul_y3k · Post by **paul_y3k** » Wed Feb 12, 2003 8:10 pm

sql server ? pah go get a proper ORACLE database and whilst we're at it go get yourself a decent operating system too. and a proper cpu and ..... ok i'll quit now lol

Robbie · Post by **Robbie** » Wed Feb 12, 2003 8:13 pm

Whats all this, a new variation of geek? I must be showing my age

Erm · Post by **Erm** » Wed Feb 12, 2003 8:16 pm

@ robbie

andyc209 · Post by **andyc209** » Thu Feb 13, 2003 10:27 am

Its still proving an arse...DISTINCT is out of the question as this will eventually have an entire companies fax log on it...

SQL server does not seem to like the MAX too much either...

To erm, who sounds puzzled at all this, this is called WORK........

MattB · Post by **MattB** » Thu Feb 13, 2003 11:38 am

What error are you getting when trying to use MAX then?

I've setup a quick test DB here and it works fine. The table I'm using is:

id d
----------- ----------------------------
1 2003-02-13 09:00:00.000
1 2003-02-14 09:00:00.000
1 2003-02-15 09:00:00.000
2 2003-02-13 10:00:00.000
2 2003-02-13 11:00:00.000
3 2003-02-16 14:00:00.000

And the query:

SELECT id, MAX(d) AS d FROM andy GROUP BY id;

returns:

id d
----------- ----------------------------
1 2003-02-15 09:00:00.000
2 2003-02-13 11:00:00.000
3 2003-02-16 14:00:00.000

Which is correct as far as I can see...

CB · Post by CB » Thu Feb 13, 2003 11:49 am

What the hell use is all this going to be to you in a few years when you're all conscripted to fight for your country! Whatcha gunna do then! Hitting the 'delete' key doesn't help. Aaaaarrggghhhhhhhhhh!"!!!!!!!!!!!

andyc209 · Post by **andyc209** » Thu Feb 13, 2003 12:06 pm

Works fine..up to a point

I have :

SELECT TOP 100 PERCENT MAX(Col004) AS Expr1, Col003
FROM dbo.faxlog
GROUP BY Col004, Col003
HAVING (MAX(Col004) < '999999') AND (Col003 = 'mpp')
ORDER BY Col004

which does filter duplicates from the Col004 colum BUT

this only shows the Col004 and Col003

(col004 being the now filtered ID and col003 being all 'MPP')

I need to also show the rest of the data as well ...col001,2,3 etc which contains names and addresses etc

e.g

SOPID Col003 Col001 Col002
123 MPP Joe blogs London
124 MPP Fred Smith Liverpool

if I add these other columns to the SELECT clause i get

Column Col001 is invalid in the select list as it is not contained in either an aggregate function or in the Group by clause

cheers