One record per ID - SQL Server Forums

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)

One record per ID

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-04-16 : 10:44:26

Hi,

I have a table which has multiple records per id:

declare @a table (id int, nme varchar(10))
insert	@a
select	1, 'nme 1' union all
select	1, 'nme 1a' union all
select	2, 'nme 2' union all
select	2, 'nme 2a' union all
select	2, 'nme 2b' union all
select	3, 'nme 3' union all
select	4, 'nme 4'

How could I extract one record per id from it? I can do

select id, max(nme)
from @a
group by id

, but I have million other columns in this table, and no PK - how do I include all those other columns?

To be clear - I don't have to have records with max(nme). I only need sample data, with one record per id.

TIA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-16 : 10:49:59

If you need only one, why cant be Min or Max of that value?

Madhivanan

Failing to plan is Planning to fail

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-04-16 : 11:58:59

quote:
If you need only one, why cant be Min or Max of that value?

Yes, it can. But the real table has over 80 columns, not 2 - how do I write the query? List all the fields but one in the group by clause with max() or min()? The result might not make sense - I'd like to have one of the original records, not a list of max or min values for each id.

Let's say my table looks as follows:

declare @a table (id int, nme varchar(10), idA int, prev_v int, nxt_v int)
insert	@a
select	1, 'nme 1',  1, 5, 3 union all
select	1, 'nme 1a', 2, 2, 4 union all
select	2, 'nme 2',  2, 5, 10 union all
select	2, 'nme 2a', 1, 2, 3 union all
select	2, 'nme 2b', 2, 2, 11 union all
select	3, 'nme 3',  3, 1, 7 union all
select	4, 'nme 4',  2, 2, 5

How do I write the query to select one record per id, with all columns? If I were to use{code]select id, max(nme), max(idA), max(prev_v), max(nxt_v) from @a
group by id, result for id=1 will be

1     'nme 1a'    2       5        4[/code] - there is no such record in the source table.

TIA

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-16 : 12:19:51

If the values in your nme column is unique per ID, you can do the following:

SELECT A.*
FROM @a A INNER JOIN (SELECT ID, MIN(NME) AS NME FROM @a GROUP BY ID) B
ON A.ID = B.ID AND A.NME = B.NME

SQL Server Helper
http://www.sql-server-helper.com

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-04-16 : 13:25:24

quote:
SELECT A.*
FROM @a A INNER JOIN (SELECT ID, MIN(NME) AS NME FROM @a GROUP BY ID) B
ON A.ID = B.ID AND A.NME = B.NME

Thanks - I guess I'll have to find a column with unique values.

Isn't there a way to fetch one record per id without having to have another column with unique values? Or maybe better - can such column be generated in the query? I remember that Peso once wrote a query for me, where he added column SEQ, which was sequence number for record within the group. I can't seem to adapt his solution here though, because I don't have date field with reliable values. Any ideas?

TIA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-16 : 13:59:18

>> Thanks - I guess I'll have to find a column with unique values.

So, are you saying that this table doesn't have a primary key? Why not? EVERY table in SQL Server should always have a primary key, otherwise it is difficult, inefficient, and/or impossible to write valid and accurate SQL statements against those tables.

- Jeff
http://weblogs.sqlteam.com/JeffS

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-04-17 : 12:03:06

quote:
So, are you saying that this table doesn't have a primary key? Why not?

There is common sense, and then there is real world

.

Actually, there is no PK, as this is staging table, populated from other systems. Final table has composite PK, and it's much easier to bring partial records in and resolve missing columns later.

Regardless of the design, I was only wondering if such "sequence" column could be generated manually. But it's not a show stopper, if there isn't any easy way to do it, then I'll live without it

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-17 : 12:41:52

What do you mean by this:

>> Final table has composite PK, and it's much easier to bring partial records in and resolve missing columns later.

When I ask "what is the primary key of this table", I am not saying that it needs to be 1 single column; as you know, a table's primary key can be multiple columns. Do multiple columns in this data define a PK?

BTW -- Even staging import tables should have at least IDENTITY primary key columns. Even in the "real world"

. You may need to copy the raw staging table into a "real" table with the identity if that's your only option, and once that is done you can access that "real" table with a true primary key and you are good to go. But more likely, a set of columns (hopefully) in this data should uniquely identify each row. Otherwise, you should really see if you can edit the export process on the other system to export all PK columns, if possible.

- Jeff
http://weblogs.sqlteam.com/JeffS

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-04-19 : 10:05:37

Thanks - I'm sure that's great advice. Source table in my case does not have PK - composite or not. Thanks anyway.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-19 : 10:09:38

quote:
Originally posted by ch9862
I'm sure that's great advice.

Translation: I am ignoring your advice.

Oh well.

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-19 : 10:34:14

I don't understand why you can't just add a identity primary key to your staging table.

There is obviously a very good reason to have it there, so what is the reason for not putting it there?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources