how to retrieve the first record in a table

Question

how to retrieve the first record in a table

gissah

SSCommitted

Points: 1815
More actions
October 24, 2011 at 8:54 am

#250596

I have a table that contains multiple transactions
column a column b
abnc 123
abnc 324
abnc 800
abnc 6400
cdns ab65
cdns 987
cdns 214
cdns gb545
I want to for each distinct(column a) which colum b transaction occurred first
myresults should look like
abnc 324
cdns 987
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

cindyaz Ten Centuries Points: 1229 More actions · Answer 1

gissah (10/24/2011)
I have a table that contains multiple transactions
column a column b
abnc 123
abnc 324
abnc 800
abnc 6400
cdns ab65
cdns 987
cdns 214
cdns gb545
I want to for each distinct(column a) which colum b transaction occurred first
myresults should look like
abnc 324
cdns 987
Thanks

I dont think it's possible unless you have another column that identifies which row was inserted first. eg a date field or an identity field.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

How do you know which one is first?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What defines that a row is 'first'?

SQL doesn't have any internal insert time or anything like that and tables have no order.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mpartridge SSC Eights! Points: 959 More actions · Answer 4

GilaMonster (10/24/2011)
What defines that a row is 'first'?
SQL doesn't have any internal insert time or anything like that and tables have no order.

Unless the table is a clustered index, in which case the table is ordered by the index key.

vikrant.a.more SSC Rookie Points: 41 More actions · Answer 5

Hi,

Here is your answered query,

please take a look.

CREATE TABLE #temp

(

[Column A] VARCHAR(10),

[Column B] VARCHAR(10)

)

INSERT INTO #temp VALUES ('abnc','123')

INSERT INTO #temp VALUES ('abnc','324')

INSERT INTO #temp VALUES ('abnc','800')

INSERT INTO #temp VALUES ('abnc','6400')

INSERT INTO #temp VALUES ('cdns','ab65')

INSERT INTO #temp VALUES ('cdns','987')

INSERT INTO #temp VALUES ('cdns','214')

INSERT INTO #temp VALUES ('cdns','gb545')

SELECT A.* FROM

(

SELECT ROW_NUMBER() OVER(PARTITION BY [COLUMN A] ORDER BY [COLUMN A],[COLUMN B]) AS ID,[Column A] ,[Column B]

FROM #temp

) A

WHERE A.ID=2

i think this output what you was expecting.

Thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

mpartridge (10/24/2011)
GilaMonster (10/24/2011)
What defines that a row is 'first'?
SQL doesn't have any internal insert time or anything like that and tables have no order.
Unless the table is a clustered index, in which case the table is ordered by the index key.

Logically only no physically. And that's still not going to sort the results when returned from the table every single time, so unless you're doing TOP (1) ... ORDER BY <clustered index key> or MIN(<clustered index key>) there's still no useful order (and you could have done those without a clustered index)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass