Is there a way to select first record...? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Is there a way to select first record...?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-25 : 09:40:48

If there are two records with same ID, is it possible to select only the first one that comes up? If there is, what is the syntax for it.

Kind of like? SELECT TOP 1 (ID) FROM Table_1

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-25 : 10:01:13

First has no meaning in a relational database. You'd have to apply an ORDER BY based on a particular column to retrieve a consistent set of data for the same query.

-------
Moo. :)

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-25 : 11:11:26

MOO Indeed....

SELECT DISTINCT [ID]?

Brett

8-)

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-25 : 14:29:31

After working with it, I found out that the old sql query was wrong. So now it is fixed.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2005-04-25 : 15:48:15

By its nature the TOP keyword will not return more than the specified amount even if matching criteria would return more than X unless the "with ties" option is used.


create table #foo
(
	recid int,
	data varchar(20)
)
go
insert into #foo
select 1, 'some stuff'
union 
select 1, 'some other stuff'
union
select 2, 'I like peas'
union
select 3, 'Today is Monday'
go
select top 1 * from #foo order by recid -- only one rec despite two lowest id numbers
go
select top 1 with ties * from #foo order by recid -- both returned records returned even though you only asked for one record
go
drop table #foo

"Hit me with a shovel 'cause I can't believe I dug you."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources