Getting First Record in each group

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)

Getting First Record in each group

Author

Topic

mpal
Starting Member

2 Posts

Posted - 2006-04-09 : 21:59:44

Hi Guys,

I was wondering if someone would have an idea how to solve the following problem.

I have the following information in my table:

A B X1
A B X2
A B X3
A B X4
A C Y1
A C X1
A C Z
D B A1
D B A2
D B A3

I would like to write a SQL statement that would create list of groups by the first two columns and show a first value from the 3rd column for each group.
In the example above, the result would look like this:

A B X1
A C Y1
D B A1

Any idea?

Thanks,

Milos

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-09 : 22:18:24

[code]select col1, col2, min(col3)
from yourtable
group by col1, col2[/code]

"I would like to write a SQL statement that would create list of groups by the first two columns and show a first value from the 3rd column for each group."
There isn't a FIRST or LAST record in the database. Records are not store in database in any particular order.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-04-09 : 22:18:40

select col1, col2, min(col3)
from YourTable
group by col1, col2

Read up on GROUP BY. It is a very powerful features of SQL.

mpal
Starting Member

2 Posts

Posted - 2006-04-09 : 23:09:45

Thanks for your replies.
I forgot to mention one more thing - the 3rd column is a text column.

I was assuming that MIN() would not work on a text column, but it seems to work.

Thanks for your replies.

Milos

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-10 : 03:29:13

>>I was assuming that MIN() would not work on a text column, but it seems to work.

Are you using SQL Server?
If the column is text, you need to convert it to varchar datatype

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-10 : 03:42:48

"I was assuming that MIN() would not work on a text column, but it seems to work."
Are you referering to datatype text or string datatype like char, varchar or nvarchar ?
If it is a text datatype, you will have to convert first as what Madhivanan suggested.

min() works not only on numeric datatype but string datatype as well. For string datatype, the min() will be based the ascii code of the characters. For example, with the samples data that you listed, the min() will be A1 for col1 = 'D' and col2 = 'B'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources