Help for a query (group by - top 0)

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)

Help for a query (group by - top 0)

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-02 : 10:52:23

I may model the question as follows

Create table #t(F1 int, F2 int, F3 int, F4 int)
Insert into #t 
Select 1,3,456,567 Union
Select 1,5,43516,15667 Union
Select 1,7,332,2467 Union

Select 2,5,0,1 Union

Select 3,3,46,567 Union
Select 3,1,87,53 Union

Select 4,1,343,457 Union
Select 4,6,4856,111

Query should output something like the "output of the" following:

Select 1,7,332,2467 Union
Select 2,5,0,1 Union
Select 3,3,46,567 Union
Select 4,6,4856,111

In other words, I need, the data of the table #t to be grouped by F1 and select any row (record) from each group.
ie.
A record from the group having F1 = 1
A record from the group having F1 = 2
A record from the group having F1 = 3
.....

The difference in the real situation is tat there r much more than 4 columns,.
It doesn't matter which record I get, I need to have one per a unique F1.

Srinika

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-02 : 11:06:38

if it really doesn't matter (i.e. not requied to be random etc..) can you not just group by that column and use a max or similar...

select F1, max(F2), max(F3), max(F4)
from #t
group by F1

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-02 : 11:16:15

Hey elancaster,

Thanks!
~~That worked.~~

Oh, I'm sorry,

When I checked with real data it failed due to the fact:
Some Columns are text, Some are bit, and some are unique identifiers.
Those cannot be used with Max()

So is there anything to this method or other (different) way to make it working!!

Thanks

Srinika

crosan
Starting Member

13 Posts

Posted - 2007-10-02 : 12:44:25

If you need the data from these fields, can you use elancasters solution with a cast statement? Say F3 was a bit field, maybe use:

select F1, max(F2), max(CAST(F3 AS INT)), max(F4)
from #t
group by F1

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-02 : 13:11:14

Hi crosan,

Thanks. This time I tested with my real data

& It worked.

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources