Need T-SQL equivalent to Access First()

Question

Need T-SQL equivalent to Access First()

tesker

SSC Rookie

Points: 34
More actions
March 18, 2010 at 1:17 pm

#220017

I have a working query in Access that uses the following:
SELECT case_sk, First(role_type) AS FirstOfrole_type, first_name, last_name
FROM q_Crystal_Setup
GROUP BY case_sk, first_name, last_name;
...and I need to create the same query in SQL Server 2005. I've read several forum posts on using the TOP command to accomplish the same thing but I can't seem to get it to work. The same person has multiple records with different roles but I only want the record for the first role that was entered for each person.
I read that Access includes the first record added when you use FIRST when there are multiple records (what I want) but that with T-SQL you also have to order by another field be able to get the first record entered (otherwise you get a random smattering of the the 1st, 3rd, 5th duplicate records, etc.) The field that would do the trick is:
case_parties_sk
but when I insert that field into the access query, I get all of the extra duplicate records - not just the top 1 as I want. I suspect that field does/b]need to be in the T-SQL version with
ORDER BY case_parties_sk
Can anyone tell me what the SQL would be to do this? Any help would be greatly appreciated!
Thanks
Tom Esker

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

You want the "First" record for this grouping, but based on what criteria? Do you have a Timestamp for the order the records were inserted in? Otherwise, there's no way to find the "first". Maybe a sequential ID, or a datetime field?

Cheers,

J-F

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

Here is a query you can use, but you will need to add a datetime field you probably have in your table in the sorting. It's just a working idea that shows you how you can filter only the first row based on a certain criteria.

;

WITH cteOrder

AS ( SELECT case_sk ,

--Generate a ID to filter out rows, based on a sorting order YOU specify.

ROW_NUMBER() OVER ( PARTITION BY case_sk, first_Name, last_Name ORDER BY "YourDatetimeField" ASC ) AS ID ,

role_type , -- You need to query this information, and it will be filtered right after

first_name ,

last_name

FROM q_Crystal_Setup

)

SELECT *

FROM cteOrder

WHERE ID = 1 -- Filter to get only the "First" match, based on the sorting criteria you applied.

Cheers,

J-F

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

J-F Bergeron (3/18/2010)
Here is a query you can use, but you will need to add a datetime field you probably have in your table in the sorting. It's just a working idea that shows you how you can filter only the first row based on a certain criteria.
;
WITH cteOrder
AS ( SELECT case_sk ,
--Generate a ID to filter out rows, based on a sorting order YOU specify.
ROW_NUMBER() OVER ( PARTITION BY case_sk, first_Name, last_Name ORDER BY "YourDatetimeField" ASC ) AS ID ,
role_type , -- You need to query this information, and it will be filtered right after
first_name ,
last_name
FROM q_Crystal_Setup
)
SELECT *
FROM cteOrder
WHERE ID = 1 -- Filter to get only the "First" match, based on the sorting criteria you applied.

A windowed function such as this would be my first suggestion to solving the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

CirquedeSQLeil (3/18/2010)
A windowed function such as this would be my first suggestion to solving the question.

Thanks for the vote of confidence Jason, 😛

We do not know if the OP has a datetime field though, I just guessed he probably has one!

Cheers,

J-F

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

J-F Bergeron (3/18/2010)
CirquedeSQLeil (3/18/2010)
A windowed function such as this would be my first suggestion to solving the question.
Thanks for the vote of confidence Jason, 😛
We do not know if the OP has a datetime field though, I just guessed he probably has one!

NP

And now it is a sit and wait for further feedback from the OP. 😎

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tesker SSC Rookie Points: 34 More actions · Answer 6

Thanks much everyone! Wow - I don't think I have ever got so much good help so quickly on a support forum before. There isn't a date field in the table but there is a sequential number field - 'case_parties_sk so I sorted on that using the sample SQL offered and that appears to have done the trick. My final SQL that worked was:

SELECT case_sk, ROW_NUMBER() OVER (PARTITION BY case_sk, first_Name, last_Name

ORDER BY case_parties_sk ASC) AS ID, role_type, first_name, last_name

FROM dbo.vCrystalDailyReport1)

SELECT *

FROM cteOrder

WHERE ID = 1

Thanks again!

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 7

J-F Bergeron

SSCrazy Eights

Points: 8593

March 18, 2010 at 2:09 pm

#1136293

You're welcome,

Cheers,

J-F

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

NP

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 9

tesker (3/18/2010)
My final SQL that worked was...

Another way, just for interest:

SELECT CDR1.*

FROM dbo.vCrystalDailyReport1 CDR1

WHERE CDR1.case_parties_sk =

(

SELECT MIN(CDR2.case_parties_sk)

FROM dbo.vCrystalDailyReport1 CDR2

WHERE CDR2.case_sk = CDR1.case_sk

AND CDR2.first_Name = CDR1.first_Name

AND CDR2.last_Name = CDR1.last_Name

);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

tesker SSC Rookie Points: 34 More actions · Answer 10

I actually understand how/why this example works. Thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 11

tesker (3/19/2010)
I actually understand how/why this example works. Thanks!

Oh good! I included it because I know some people find that sort of query construction more natural.

It produces an efficient execution plan too, which is a bonus.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi