how can I generate multi-distincts with one stmt

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)

how can I generate multi-distincts with one stmt

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-08-29 : 10:43:56

I have a very large table (millions of records), and I would like to present my intranet users with a filtering system

the filtering system I'm talking about would be a series of listboxes that contain the DISTINCT values for a series of fields (ie the partnumber listbox would contain the distinct partnumber values in the table, the brand would contain the distinct brand values in the table, etc).

has anyone come up with ANY other way to get the data other than by doing multiple "SELECT DISTINCT whatever..." statements (ie one for each field/listbox)????

given that there are multiple fields to get distinct values for, and each SELECT DISTINCT could take quite a while to generate, I was wondering if there was a way to do this using a single statement to maximize performance

possibly using coalesce or ???

thought that there might be a way to use coalesce to summarize each field, except that it would produce repeated entries.

thought about using a case stmt that searches the coalesced field before performing the next coalesce but ....

any suggestions (other than precreating a cube of the data - cubes have given me a headache lately)

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-08-29 : 10:59:16

I think with the size of your table, preaggregation of the drop-downs is going to be the best approach. You will frustrate your users if they have to wait for the page to load while the disticts are being generated.

A possible approach is to generate separate tables with these distinct values at predetermined times. Then the drop-downs reference these small tables.

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-29 : 11:02:11

Normally, if your data is normalize, you should have a table for your brand with a relation on your big table that have millions of records. So you should not do a distinct on the big table but a normal select on your brand table

gdeconto
Posting Yak Master

107 Posts

Posted - 2002-08-29 : 11:24:51

thx anyhow guys.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-29 : 14:23:56

I agree with what everyone else said -- if you have millions of rows in your table then it's unlikely that you can deliver the distinct values straight from the table in a timely way. But to answer your question, can you get the distinct values for several columns in one select (and one scan of the table), yes, it's possible:


SELECT DISTINCT col,
  CASE
    WHEN col = 'col1' THEN col1
    WHEN col = 'col2' THEN col2
  END AS value
FROM BigTable
CROSS JOIN (
    SELECT 'col1' AS col
    UNION ALL SELECT 'col2'
  ) AS Columns

Interestingly, SQL Server 2000 will, if the estimated cardinality of (col1, col2) is low enough aggregate before the join.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources