Selecting the first occurence of a row

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 2000 Forums

SQL Server Development (2000)

Selecting the first occurence of a row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-27 : 11:07:40

Deborah writes "I am using SQL Server 7.0 (SP 3) on Windows NT.

 sample   cycle  idtype     sample_date   res1    res2
-------   ---   ------    -----------    ---     ----
6861	    1	  M	  05/17/1999	  D	 <NULL>
6080	    1	  M	  11/17/2000	  A	   P
6080	    1	  M	  05/19/1999	  A	   J
6140	    1	  C	  01/06/2000	  D	   P
6140	    1	  M	  06/23/1999	  D	   J
6806	    1	  C	  08/08/2001	  A	   P

I need to select the first occurence of each sample. My results should be:

 sample   cycle  idtype     sample_date   res1    res2
-------   ---   ------    -----------    ---     ----
6861	    1	  M	  05/17/1999	  D	 <NULL>
6080	    1	  M	  11/17/2000	  A	   P
6140	    1	  C	  01/06/2000	  D	   P
6806	    1	  C	  08/08/2001	  A	   P

Your help would be greatly appreciated."

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 13:46:02

How do you define "FIRST"? SQL Server does not store the records in any particular order unless you have created a clustered index, in which case they are stored in order of that indexed field. Based on your desired results, you're not defining "first" as being in order of sample_date. Is there some other datestamp or Identity field that would indicate the order? Help us define that, and we can help you with a SELECT statement.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

dsdeming

479 Posts

Posted - 2001-12-27 : 14:07:19

How about something like this:

SELECT a.sample, a.cycle, a.idtype, a.sample_date, a.res1, a.res2
FROM tablename a
JOIN ( SELECT sample, MIN( sample_date ) AS sample_date
FROM tablename GROUP BY sample ) b
ON a.sample = b.sample AND a.sample_date = b.sample_date

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 14:24:12

But in the desired resultset in the original question, on sample 6140, Deborah asks for the row that has the LARGER date, not the smaller one. So doing a MIN(sample_date) would return the wrong row.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

dsdeming

479 Posts

Posted - 2001-12-27 : 14:47:53

You're right. It looks as though substituting MAX for MIN in my example will produce the desired results. However, it seems to me that unless we know what ( if anything ) was in the ORDER BY clause of the SELECT that returned the first results, we can't reliably return the first occurrence of any value.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 16:01:49

I agree. With this small data sample, the MAX function would return the specified resultset. But as you mentioned it still begs the question of whether that is consistently the definition of First. We may never know...

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

bh14932
Starting Member

2 Posts

Posted - 2001-12-28 : 01:40:13

May I suggest the use of "Top" within the sub query???...

or as an alt:-

create a temp table with a row id as an indication of order

select sample ,cycle, idtype, sample_date, res1, res2,rowid = IDENTITY(int, 1, 1) into #x from tablename

just a thought...

bh14932
Starting Member

2 Posts

Posted - 2001-12-28 : 01:40:20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources