How to have distinct value? - 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

General SQL Server Forums

New to SQL Server Programming

How to have distinct value?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-09-01 : 14:27:14

Hi

I have table 'TblProd' and columns ID, categories, make, description, model. I am using the below procedure


create procedure getsubcategories
@category nvarchar (32)
As
Select ID, make, model from TblProd where category = @category
GO

My question how do I get distinct 'make' and other respective columns.

advance thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-01 : 14:44:17

use distinct keyword.

and know that having duplicates in your table is a sign of bad design.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-01 : 15:50:10

You can either use DISTINCT or GROUP BY the columns...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-09-02 : 09:33:21

Hi

I have table like below and I know how to get distinct values from one column. BUT I want get values from more than 3 column and dintinct value from particular colum (make).

ID  Category   make          model      
01  TV         sony           Ng6788
02  TV         Panasonic      PN678
03  TV         sony           SN435
04  Charger    sony           ch 567 
05  Charger    fujitsu        Fu780
06  charger    fujitsu        FG234R
07  TV         sony           SN56TY

MY QUESTION IS HOW TO GET distinct 'MAKE' AND all model,id WHERE CATEGORY IS = @category LIKE BELOW:

make     id     model
----    ----    ---------
sony      01    Ng6788
panasonic 02    PN678
fujitsu   05    Fu780

I DON'T NEED LIKE BELOW

make     id     model
----    ----    ---------
sony      01    Ng6788
panasonic 02    PN678
sony      03    SN435
sony      07    SN56TY
fujitsu   05    Fu780
...       ..    .....
....      ...    ....

Please help thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-02 : 09:41:24

[code]SELECT t.*
FROM TblProd t
INNER JOIN
(
SELECT ID = MIN(ID), make
FROM TblProd
GROUP BY make
) m
ON t.ID = m.ID[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-02 : 09:44:41

or if you are using SQL 2005, you can also try this

SELECT	*
FROM
(
	SELECT	*,
		seq_no = row_number() OVER (PARTITION BY make ORDER BY ID)
	FROM	TblProd
) t
WHERE	t.seq_no	= 1

KH
[spoiler]Time is always against us[/spoiler]

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-09-02 : 09:57:11

Hi

I have tried but getting error message(INCORRECT SYNTAX). I am using sql procedure. What is 't' stands for

quote:
Originally posted by khtan

SELECT t.*
FROM TblProd t
INNER JOIN
(
SELECT ID = MIN(ID), make
FROM TblProd
GROUP BY make
) m
ON t.ID = m.ID

KH
[spoiler]Time is always against us[/spoiler]

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-09-02 : 10:05:48

HI

It works. Thanks a lot. Please ignore my previous reply about SYNTAX ERROR. Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-02 : 10:19:54

t and m are the table alias

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 05:54:33

quote:
Originally posted by Vaishu

HI

It works. Thanks a lot. Please ignore my previous reply about SYNTAX ERROR. Thank you.

Post the full error you got

Madhivanan

Failing to plan is Planning to fail

ananiraj
Starting Member

1 Post

Posted - 2007-09-11 : 06:16:32

Hi,
Could you Tell how this works..

SELECT *
FROM
(
SELECT *,
seq_no = row_number() OVER (PARTITION BY make ORDER BY ID)
FROM TblProd
) t
WHERE t.seq_no = 1

Thank You,
Raja.D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 06:25:39

Read Books Online (the SQL Server help file).
If you haven't done so yet, now is the time to make yourself familiar to this excellent resource.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources