Return records using DISTINCT or UNIQUE Predicates

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)

Return records using DISTINCT or UNIQUE Predicates

Author

Topic

timothymannah
Starting Member

14 Posts

Posted - 2006-11-12 : 19:07:05

I know its to do with DISTINCT & UNIQUE Predicates.
I want to return only the 1st instance of the GR_ID
except when the GR_ID is NULL. I know that DISTINCT
treats nulls the same if there is 2 or more. So I need
to use unique.

Below are a set of records:

ST_ID DESCRIPTION PRICE GR_ID
-----------------------------------------------------
SD001 BD Safeclip Device 12.73 NULL
SD002 BD Sharps Collector 1.4L 4.19 GR05
SD003 BD Sharps Collector 3.1L 6.1 GR05
SD004 Needle Safe-Desk Top 3.73 NULL
SD005 Sharps Collector 1L 4.55 GR06
SD006 Sharps Collector 1.7L 5.19 GR07
SD007 Sharps Collector 4L - RE4LR 6.82 GR07
SD008 Sharps Collector 4L 6.91 GR07
SD009 Sharps Collector 10L - RE10LS 17.46 GR07
SD010 Sharps Collector 16.5L- RE15LS 20.73 NULL
SD011 Sharps Collector 0.5L 3.73 GR06

I want to return these:

ST_ID DESCRIPTION PRICE GR_ID
-----------------------------------------------------
SD001 BD Safeclip Device 12.73 NULL
SD002 BD Sharps Collector 1.4L 4.19 GR05
SD004 Needle Safe-Desk Top 3.73 NULL
SD005 Sharps Collector 1L 4.55 GR06
SD006 Sharps Collector 1.7L 5.19 GR07

BUT, I dont just want to return:

GR_ID
-----
NULL
GR05
NULL
GRO6
GR07

So i know i cant just do the

SELECT UNIQUE GR_ID
From StockMaster
Where BLAH BLAH

AND

SELECT UNIQUE *
From StockMaster
Where BLAH BLAH

Doesnt work obviously....Any ideas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 20:36:02

[code]
select *
from StockMaster s
inner join
(
select min_ST_ID = min(ST_ID), GR_ID
from StockMaster
group by GR_ID
) m
on s.ST_ID = m.min_ST_ID
[/code]

timothymannah
Starting Member

14 Posts

Posted - 2006-11-12 : 20:52:48

Where can I add the criteria of WHERE ST_ID LIKE 'SD%' thats how i get the initial records:
ST_ID DESCRIPTION PRICE GR_ID
-----------------------------------------------------
SD001 BD Safeclip Device 12.73 NULL
SD002 BD Sharps Collector 1.4L 4.19 GR05
SD003 BD Sharps Collector 3.1L 6.1 GR05
SD004 Needle Safe-Desk Top 3.73 NULL
SD005 Sharps Collector 1L 4.55 GR06
SD006 Sharps Collector 1.7L 5.19 GR07
SD007 Sharps Collector 4L - RE4LR 6.82 GR07
SD008 Sharps Collector 4L 6.91 GR07
SD009 Sharps Collector 10L - RE10LS 17.46 GR07
SD010 Sharps Collector 16.5L- RE15LS 20.73 NULL
SD011 Sharps Collector 0.5L 3.73 GR06

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 20:56:28

[code]
select *
from StockMaster s
inner join
(
select min_ST_ID = min(ST_ID), GR_ID
from StockMaster
WHERE ST_ID LIKE 'SD%'
group by GR_ID
) m
on s.ST_ID = m.min_ST_ID
[/code]

timothymannah
Starting Member

14 Posts

Posted - 2006-11-12 : 21:15:55

The problem is that this does not show all the NULL values in GR_ID

Some items do not have a GR_ID how can i include these also remembering that:

ST_ID DESCRIPTION PRICE GR_ID
-----------------------------------------------------
SD001 BD Safeclip Device 12.73 NULL
SD002 BD Sharps Collector 1.4L 4.19 GR05
SD003 BD Sharps Collector 3.1L 6.1 GR05
SD004 Needle Safe-Desk Top 3.73 NULL
SD005 Sharps Collector 1L 4.55 GR06
SD006 Sharps Collector 1.7L 5.19 GR07
SD007 Sharps Collector 4L - RE4LR 6.82 GR07
SD008 Sharps Collector 4L 6.91 GR07
SD009 Sharps Collector 10L - RE10LS 17.46 GR07
SD010 Sharps Collector 16.5L- RE15LS 20.73 NULL
SD011 Sharps Collector 0.5L 3.73 GR06

is what i started with and

ST_ID DESCRIPTION PRICE GR_ID
-----------------------------------------------------
SD001 BD Safeclip Device 12.73 NULL
SD002 BD Sharps Collector 1.4L 4.19 GR05
SD004 Needle Safe-Desk Top 3.73 NULL
SD005 Sharps Collector 1L 4.55 GR06
SD006 Sharps Collector 1.7L 5.19 GR07
SD010 Sharps Collector 16.5L- RE15LS 20.73 NULL

is what i need in the end (including records with null values)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 21:51:56

please explain the critiria. The result that you want is based on ? ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 21:54:43

is it GR_ID & DESCRIPTION ? ? ?


select *
from   StockMaster s
       inner join
       (
           select min_ST_ID = min(ST_ID), GR_ID, DESCRIPTION
           from   StockMaster
           WHERE ST_ID LIKE 'SD%'
           group by GR_ID, DESCRIPTION
       ) m
       on  s.ST_ID = m.min_ST_ID

timothymannah
Starting Member

14 Posts

Posted - 2006-11-12 : 22:00:18

I am trying to select records from a table called StockMaster

The initial select was

Select *
From Stockmaster
where ST_ID Like 'SD%'

this returned

ST_ID DESCRIPTION PRICE GR_ID
-----------------------------------------------------
SD001 BD Safeclip Device 12.73 NULL
SD002 BD Sharps Collector 1.4L 4.19 GR05
SD003 BD Sharps Collector 3.1L 6.1 GR05
SD004 Needle Safe-Desk Top 3.73 NULL
SD005 Sharps Collector 1L 4.55 GR06
SD006 Sharps Collector 1.7L 5.19 GR07
SD007 Sharps Collector 4L - RE4LR 6.82 GR07
SD008 Sharps Collector 4L 6.91 GR07
SD009 Sharps Collector 10L - RE10LS 17.46 GR07
SD010 Sharps Collector 16.5L- RE15LS 20.73 NULL
SD011 Sharps Collector 0.5L 3.73 GR06

I wanted to refine the returned results so that ONLY 1 of the records with the SAME GR_ID was returned and ALL NULL values were returned also to like like this:

ST_ID DESCRIPTION PRICE GR_ID
-----------------------------------------------------
SD001 BD Safeclip Device 12.73 NULL
SD002 BD Sharps Collector 1.4L 4.19 GR05
SD004 Needle Safe-Desk Top 3.73 NULL
SD005 Sharps Collector 1L 4.55 GR06
SD006 Sharps Collector 1.7L 5.19 GR07
SD010 Sharps Collector 16.5L- RE15LS 20.73 NULL

I think this works

SELECT StockMaster.* FROM StockMaster INNER JOIN
(SELECT Min(ST_ID) AS ST_ID FROM StockMaster
WHERE StockCode LIKE 'SD%' AND GR_ID IS NOT NULL
GROUP BY GR_ID) tblMin ON StockMaster.ST_ID = tblMin.ST_ID
UNION
SELECT StockMaster.*
WHERE StockCode LIKE 'SD%' AND GR_ID IS NULL

What do you think?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 22:13:15

[code]
select s.*
from Stockmaster s
inner join
(
select GR_ID, min_ST_ID = min(ST_ID)
from Stockmaster
where GR_ID is not null
group by GR_ID
)m
on s.ST_ID = m.min_ST_ID
where s.ST_ID like 'SD%'

union all

select s.*
from Stockmaster s
where s.ST_ID like 'SD%'
and s.GR_ID is null
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 22:15:43

Another way


select	s.*
from	Stockmaster s
	inner join
	(
		select	GR_ID, min_ST_ID = min(ST_ID)
		from	Stockmaster
		where	GR_ID	is not null
		group by GR_ID

		union all

		select	GR_ID, min_ST_ID = ST_ID
		from	Stockmaster
		where	GR_ID	is null
	) m
	on	s.ST_ID	= m.min_ST_ID
where	s.ST_ID 	like 'SD%'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 22:18:19


select	*
from	Stockmaster s
where	s.ST_ID 	like 'SD%'
and     (
        s.ST_ID = (select min(ST_ID) from Stockmaster x where x.GR_ID = s.GR_ID)
or	s.GR_ID	is null
        )

timothymannah
Starting Member

14 Posts

Posted - 2006-11-12 : 22:31:01

Thank you so much...you have been an amazing help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 22:45:07

try out the different method and see which gives you the best performance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources