Dumb question on getting unique data

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)

Dumb question on getting unique data

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2002-02-19 : 10:57:08

Hi,

I know this is pretty standard stuff, but I'd appreciate it if someone out there could help ...

If I have various linked tables in my database, how can I contruct my queries to get 'unique' data out of the system. I mean when you query across a set of linked tables you often get repeats of large chunks of the data with the differences only existing in one or two columns. Any way I can squeeze these into unique records?

I have a feeling I'm not going to like the answer :)

izaltsman
A custom title

1139 Posts

Posted - 2002-02-19 : 11:08:44

You need to figure out what to do with the non-unique fields... A lot of times you can simply take the MIN or the MAX value -- but it all depends on what business rules dictate... So if MIN is acceptable to you, your query would look something like:

SELECT uniq_fld1, uniq_fld2, MIN(non_uniq_fld)
FROM mytable
GROUP BY uniq_fld1, uniq_fld2

Edited by - izaltsman on 02/19/2002 11:09:47

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-19 : 12:32:35

In general if this is happenning it means that there is something wrong with the query or the database structure.
The query may be returning too many rows because it has missed a join or it is having to join to tables and getting redundant rows because the database isn't correctly normalised for the data.

Saying that though there are times where it is better design to compromise a few queries rather than have a complicated database.
It depends what you actually want back from the query.
If you don't want the differing fields then omit them from the result set and use a distnct query.
If there is just one of the records that you want then use a correlated subquery

select * from tbl1,tbl2
where tbl1.id = tbl2.id
and tbl2.fld = (select max(fld2) from tbl2 t2 where t2.id = tbl2.id)

if you want an aggregate of the values then the group by given by izaltsman will work.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources