brain gone dead- help!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

brain gone dead- help!!

Author

Topic

NicJ
Starting Member

15 Posts

Posted - 2009-06-10 : 04:47:32

I am having SQL fun with a database conversion from an old in house ISAM database that does not respect the usual relational rules.

My SQL is a little rusty

and I can't remember how to acheive the following

I have a table (acprlog) with 4 columns:-

Serial Part test date Test Time

313295 68TZ12 2009-05-18 12:54:12.0000000
313295 68TZ12 2009-05-18 16:36:22.0000000
313295 46KL04 2009-01-19 08:45:44.0000000
313295 91PU11 2008-08-19 16:01:04.0000000

As you can see the same serial is used for the various parts and
each can be "tested" more than once a day.

I need to extract just the Serial and Part number and test details for the latest dated test for each distinct serial IE

Serial Part test date Test Time
313295 68TZ12 2009-05-18 16:36:22.0000000

now using

select distinct serial, max(part), Max(testdate),max(testtime)
from acprlog
group by serial

I will get:-

Serial Part test date Test Time
313295 91PU11 2009-05-18 16:36:22.0000000

A complete mixture of the records

What I want is:-

Select serial,part,testdate,testtime
from acprlog
where

testtime and date is the largest for this serial.

PLease remind me....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 04:54:45

So it is sql server 2008 because you have date and time datatype?

No, you're never too old to Yak'n'Roll if you're too young to die.

NicJ
Starting Member

15 Posts

Posted - 2009-06-10 : 04:57:39

quote:
Originally posted by webfred

So it is sql server 2008 because you have date and time datatype?

Yes - it is now - I managed to get raw data from the original DB and I inserted the data into a server 2008 database created for the purpose - now we have sql access we are trying to extract usable data and this is a prime example.
cheers

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 05:05:28

So you want exactly this output
313295 68TZ12 2009-05-18 16:36:22.0000000
whatever part exists for this serial?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 05:05:58

[code]SELECT Serial,
Part,
TestDate,
TestTime
FROM (
SELECT Serial,
Part,
TestDate,
TestTime,
ROW_NUMBER() OVER (PARTITION BY Serial, Part ORDER BY TestDate DESC, TestTime DESC) AS recID
FROM Table1
) AS d
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

NicJ
Starting Member

15 Posts

Posted - 2009-06-10 : 05:10:55

quote:
Originally posted by webfred

So you want exactly this output
313295 68TZ12 2009-05-18 16:36:22.0000000
whatever part exists for this serial?

I want the part number and the corresponding dat and time for the serial number (but only the highest date and time).

so

serial part date time
1 2 01/01/2009 00:00:00
1 2 02/02/2009 09:00:00
1 3 03/03/2009 06:00:00

I would only want the thid line

1 3 03/03/02009 06:00:00

because its the latest date and time

Cheers

Nic

"I know that you believe that you understand what you think I said, but I am not sure you realise that what you heard is not what I meant..."

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 05:13:32

quote:

Originally posted by Peso

SELECT	Serial,
	Part,
	TestDate,
	TestTime
FROM	(
		SELECT	Serial,
			Part,
			TestDate,
			TestTime,
			ROW_NUMBER() OVER (PARTITION BY Serial, Part ORDER BY TestDate DESC, TestTime DESC) AS recID
		FROM	Table1
	) AS d
WHERE	recID = 1

E 12°55'05.63"
N 56°04'39.26"

No, you're never too old to Yak'n'Roll if you're too young to die.

NicJ
Starting Member

15 Posts

Posted - 2009-06-10 : 05:19:32

quote:

Originally posted by webfred

[quote]Originally posted by Peso

SELECT	Serial,
	Part,
	TestDate,
	TestTime
FROM	(
		SELECT	Serial,
			Part,
			TestDate,
			TestTime,
			ROW_NUMBER() OVER (PARTITION BY Serial, Part ORDER BY TestDate DESC, TestTime DESC) AS recID
		FROM	Table1
	) AS d
WHERE	recID = 1

Well thats spot on!!!

Thanks - now any chance you would care to share what the flip that does and why? I have never user OVER or PARTITION by before - are they 2008 sql functions or am I so out of data I need to get a new book?

"I know that you believe that you understand what you think I said, but I am not sure you realise that what you heard is not what I meant..."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 05:24:17

The Windowed Functions were introduced in SQL Server 2005.

E 12°55'05.63"
N 56°04'39.26"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 05:25:51

ROW_NUMBER() is available since SQL Server 2005.
PARTITION BY is used (in my words) to define where the rownum has to start over with value 1.

No, you're never too old to Yak'n'Roll if you're too young to die.

NicJ
Starting Member

15 Posts

Posted - 2009-06-10 : 05:27:16

If I could give out points I would.

Thanks guys - especially PESO for the final solution.

Its back to the books for me now!

Cheers

Nic

"I know that you believe that you understand what you think I said, but I am not sure you realise that what you heard is not what I meant..."

NicJ
Starting Member

15 Posts

Posted - 2009-06-10 : 05:31:25

quote:
Originally posted by webfred

ROW_NUMBER() is available since SQL Server 2005.
PARTITION BY is used (in my words) to define where the rownum has to start over with value 1.

SQL server has manuy new functions I need to get up to speed with - But it can't seem to match the ease of use of:-

unload to c:\myfile.txt delimiter ","
select * from table;

That Informix used to have (may still - its been a while).

A real shame - I know BCP can do it - but its not the same as having the function within the SQL script.

cheers again

Nic

"I know that you believe that you understand what you think I said, but I am not sure you realise that what you heard is not what I meant..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources