SQL Query

Ziycon

Say i have the below data, is it possible to select the second record for the id number from all the id numbers(I've highlighted the records that i want to select)?

id name date
100 test 01/01/08 11:06
100 test 01/01/08 11:08
101 car 06/01/08 16:43
101 car 06/01/08 16:47
101 car 07/01/08 09:21
101 car 11/01/08 11:16
102 house 08/01/08 09:53
102 house 08/01/08 15:11
102 house 09/01/08 13:06
103 boat 09/01/08 10:03
103 boat 09/01/08 10:07
103 boat 09/01/08 10:34

Any help appreciated.

keeffe2001

how do you mean "second"? Is the table ordered in a certain way (do you want the second most recent time?)

jon1981

i think he means the second occurance for each ID number, but my sql aint great, if it were perl it would be alittle easier

Ziycon

keeffe2001 wrote: »

how do you mean "second"? Is the table ordered in a certain way (do you want the second most recent time?)

Ye, trying to get the second record after the initial record, i've narrowed the query down but it displays multiple rows for each record and i only what the second record created for each id number.

Tom Dunne

What database?

I could do it in Oracle, but if you are not using Oracle, that wouldn't be much good to you, would it?

marco_polo

This cannot be done in pure SQL as the notion of the position of a record does not exist, the only thing that distinguishes the records you are trying to extract is the date and time, so unless you know this at the beginning there is no way to pinpoint it. Depending on what DB you are using there may be a way to do this using something like pl/sql or a proceedure function. The other alternative is to use a programming language.

But even at that I don't think there is any guarantee that the records will be returned in the same order each time.

Ziycon

Tom Dunne wrote: »

What database?

I could do it in Oracle, but if you are not using Oracle, that wouldn't be much good to you, would it?

Sorry about that, my heads not screwed on this morning, its MSSQL.

marco_polo

Ziycon wrote: »

Sorry about that, my heads not screwed on this morning, its MSSQL.

Look into T-SQL it is the equivalent of pl/sql for MSServer, basically it is a proprietary extention of sql. I would be very surprise if it is not possible to achieve what you want to do using that.

The construct you would be looking for is called a cursor in pl/sql which allows you to navigate through returned records. I am sure there should be something similar in T-Sql.

I don't know too much about it to be of any further help TBH.

Webmonkey

I'm half asleep but can you not do something like:

SELECT DISTINCT id,name FROM table ORDER BY Date DESC

Edit:
Forget it - that will just find the last occurance - not the second.

You could do it with PL/SQL: check out: http://www.boards.ie/vbulletin/showpost.php?p=55268957&postcount=5

You could do an IF statement to check if the counter is two then do a select and reset the counter once it reaches this. Only an idea - I've never done a conditional select but might point you on the right track.

seamus

You can do a self join/subquery, but TSQL may have something more flexible in it;

select 
	q1.id, q1.name, q2.date
FROM 
	table q1,
	(SELECT id, MAX(date) FROM table GROUP BY id) q2
WHERE q1.id = q2.id

Not sure if the syntax is correct

Tom Dunne

Webmonkey wrote: »

You could do it with PL/SQL: check out: http://www.boards.ie/vbulletin/showpost.php?p=55268957&postcount=5

You could do an IF statement to check if the counter is two then do a select and reset the counter once it reaches this. Only an idea - I've never done a conditional select but might point you on the right track.

Just to follow on from this, what you are looking for is an additional column with a number in it (as Webmonkey indicated above):

id name date COUNTER_COL
100 test 01/01/08 11:06 1
100 test 01/01/08 11:08 2
101 car 06/01/08 16:43 1
101 car 06/01/08 16:47 2
101 car 07/01/08 09:21 3
101 car 11/01/08 11:16 4
102 house 08/01/08 09:53 1
102 house 08/01/08 15:11 2
102 house 09/01/08 13:06 3
103 boat 09/01/08 10:03 1
103 boat 09/01/08 10:07 2
103 boat 09/01/08 10:34 3

Then, all you do is select from that dataset, where COUNTER_COL=2.

Simple.

seamus

Do'h, missed the "second" requirement.

Ziycon

Thanks for all the replies, i've been working away at it and im looking into T-SQL, found out now, that its not always the 2 record for the id thats needed it could be the 3rd or 4th!

kayos

Ziycon wrote: »

Thanks for all the replies, i've been working away at it and im looking into T-SQL, found out now, that its not always the 2 record for the id thats needed it could be the 3rd or 4th!

Would I be right in saying you are looking for the latest record? With the data you have given us there is no other logical "requirement" as the date is the only difference in the rows for the same ID.

stylers

you will need another column with a unique ID in it, where you could pick out odd/even rows or increments of rows etc per set of ID's. oracle has a rownum you could use but you 'aint using oracle, and there's no guarantee what order your data would come out. as said previously, there is nothing in your data that can identify the second row per same ID.

showry

Doesn't sql server 2005 have analytics in it? I've used it in Oracle for this kind of thing.

I haven't got a database in front of me right now but it would be something like

select * from
(select id, name, date, rank() over (partition by id order by date desc) maxno)
where maxno = 1

showry

this should give you the second row of each group (assuming you're ordering by date)

SELECT t1.id, t1.name, MIN( t1.date )
FROM t_test t1
WHERE t1.date > (
SELECT MIN( t2.date )
FROM t_test t2
WHERE t2.id = t1.id )
GROUP BY t1.id, t1.name

Webmonkey

showry wrote: »

this should give you the second row of each group (assuming you're ordering by date)

SELECT t1.id, t1.name, MIN( t1.date )
FROM t_test t1
WHERE t1.date > (
SELECT MIN( t2.date )
FROM t_test t2
WHERE t2.id = t1.id )
GROUP BY t1.id, t1.name

He has already said that it doesn't necessarily have to be the 2nd row. Still waiting on what actually does identify the row.