November 25, 2002 at 8:28 am
We have this table:
CREATE TABLE [dbo].[allocdet] (
[alloc_num] [decimal](18, 0) NOT NULL ,
[price_num] [decimal](18, 0) NOT NULL ,
[serv_prlv] [int] NOT NULL ,
[serv_desc] [int] NOT NULL ,
[start_on] [tinyint] NOT NULL ,
[day_length][int] NOT NULL ,
[day_pr_pkg][int] NOT NULL ,
[flags] [tinyint] NOT NULL ,
[agent_num] [int] NOT NULL
) ON [PRIMARY]
We need to select all the fields from rows that have a distinct values on price_num, serv_prlv, agent_num.
How can we do that in one select?
Do we need to do a select into temporary table "SELECT DISTINCT price_num, serv_prlv, agent_num FROM allocdet ORDER BY price_num, serv_prlv, agent_num" and later to do "SELECT TOP 1 * FROM allocdet" WHERE the values mach each line in the previous select.
In some databases there is a unique index that does that but not in MSSQL
November 25, 2002 at 8:38 am
Do you want the first row for an occurrence of price_num, serv_prlv, agent_num. Do you have a primary key on the table?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 25, 2002 at 8:58 am
If the table has:
alloc_num, price_num, serv_prlev, agent_num, ...other fields
--------------------------------------------------------------------------------------
1 , 127 , 1 , 0 , ......, ....
2 , 127 , 1 , 0 , ......, ....
3 , 127 , 1 , 4 , ......, ....
4 , 127 , 1 , 4 , ......, ....
I expect to get the all the fields from the lines with alloc_num 1 and 3
Avron
November 25, 2002 at 9:13 am
See if this works.......
select allocdet.*
from (select min(alloc_num) alloc_num, price_num, serv_prlv, agent_num from allocdet group by price_num, serv_prlv, agent_num) a
inner join allocdet on a.alloc_num = allocdet.alloc_num and a.price_num = allocdet.price_num and a.serv_prlv = allocdet.serv_prlv and a.agent_num = allocdet.agent_num
November 25, 2002 at 9:21 am
sorry, this is not what i ment.
when i do: "SELECT DISTINCT price_num, serv_prlv, agent_num FROM allocdet ORDER BY price_num, serv_prlv, agent_num".
this get me the correct lines.
but i want ALL the fields from those lines.
November 25, 2002 at 9:29 am
Once you have performed the SELECT DISTINCT price_num, serv_prlv, agent_num.... you have a subset of the original table.
If you then want to extract the information contained in the other fields corresponding to the distinct list you have generated, you have to make a decision as to which of the corresponding rows in the main table you wish to take the rest of the fields from. In my example I presumes that the alloc_num fields governed the sequence of the records and by using the MIN() function you can get the first corresponding value, using MAX() would get you the last corresponding value.
How did you plan to decide which of the rows in the main table would supply the remaining fields corresponding to the values in you distinct subset of data?
November 25, 2002 at 9:36 am
Not realy care.
anny accurance is good. just one accurance of each row that have those distinct values.
November 25, 2002 at 9:40 am
So why doesn't this work.....
select allocdet.*
from (select min(alloc_num) as alloc_num, price_num, serv_prlv, agent_num from allocdet group by price_num, serv_prlv, agent_num) a
inner join allocdet on a.alloc_num = allocdet.alloc_num and a.price_num = allocdet.price_num and a.serv_prlv = allocdet.serv_prlv and a.agent_num = allocdet.agent_num
....it is creating a distinct list (using GROUP BY instead of DISTINCT) and the first corresponding alloc_num, then joining back to the main allocdet table to return all the fields?
November 25, 2002 at 9:57 am
This will do
select allocdet.*
from (select min(alloc_num) as alloc_num
from allocdet
group by price_num, serv_prlv, agent_num) a
inner join allocdet on a.alloc_num = allocdet.alloc_num
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 25, 2002 at 9:59 am
Going back to test it
November 25, 2002 at 10:05 am
You are quite right simon, you only need to join using the one field, providing that the alloc_num field is unique, which Avron has not confirmed.
November 25, 2002 at 10:08 am
THANKS - Its working.
I will start learning more about grouping today.
December 3, 2002 at 8:02 pm
We recently did the same thing but took a slightly unusual approach. We created a table valued user-defined function that basically did the select distinct() function portion, then joined that with the base table selected, in your case, the highest alloc_num value
select *
from allocdet a
join udfDistinctones() b
on a.price... = b.price...
and a.serv.... = b.server...
and a.alloc_num =
(select max(alloc_num)
from allocdet InnerTbl
where innertbl.price_num=a.priceNum
and innerTbl.serv=b....
etc.
)
it was a little hairy but it works pretty fast. And it gives you control over which row you get back , the "earliest" or the "latest" row depending on how you set alloc_num. In our case, it was an increasing IDENTITY value so the lowest was always the earliest and the highest always most recent.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply