Help with Sql Statement - SELECT DISTINCT

I'm trying to return 8 column where just a single column (WKSHTNUM) is Distinct but I'm not having any luck.  Can someone have a look at my SQL and tell me what I'm doing wrong?
SELECT [WKSHTNUM]
      ,[LINENUMBER]
      ,[SERVICE]
      ,[SUPPLIER]
      ,[OLDSUPPLIER]
      ,[MARGIN]
      ,[COST_EST]
      ,[COST_ACT]
  FROM [MY_TABLE]
  WHERE [WKSHTNUM] IN (SELECT DISTINCT [WKSHTNUM] FROM [MY_TABLE])
  ORDER BY [WKSHTNUM]

Open in new window

cdemott33Asked:
Who is Participating?
 
Ephraim WangoyaCommented:
try
;with cte
as
(
SELECT [WKSHTNUM]
      ,[LINENUMBER]
      ,[SERVICE]
      ,[SUPPLIER]
      ,[OLDSUPPLIER]
      ,[MARGIN]
      ,[COST_EST]
      ,[COST_ACT]
      ,ROW_NUMBER() OVER (PARTITION BY WKSHTNUM ORDER BY LINENUMBER)RN 
  FROM [MY_TABLE]
)

SELECT * FROM cte WHERE RN = 1

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to check this article:
http://www.experts-exchange.com/A_3203.html
0
 
HainKurtSr. System AnalystCommented:
your query is exactly same as

SELECT [WKSHTNUM]
      ,[LINENUMBER]
      ,[SERVICE]
      ,[SUPPLIER]
      ,[OLDSUPPLIER]
      ,[MARGIN]
      ,[COST_EST]
      ,[COST_ACT]
  FROM [MY_TABLE]
 ORDER BY [WKSHTNUM]

so what are you trying to do? create an excel sheet and show a sample data and what you are trying to get from this sample
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
manishkungwaniCommented:
To get the distinct values from only 1 column, use this :

SELECT DISTINCT([WKSHTNUM])
      ,[LINENUMBER]
      ,[SERVICE]
      ,[SUPPLIER]
      ,[OLDSUPPLIER]
      ,[MARGIN]
      ,[COST_EST]
      ,[COST_ACT]
  FROM [MY_TABLE]
 ORDER BY [WKSHTNUM] 

Open in new window


This is a direct way to do it.
0
 
lcohanDatabase AnalystCommented:
Is this what you wanted?

SELECT DISTINCT
       [WKSHTNUM]
      ,[LINENUMBER]
      ,[SERVICE]
      ,[SUPPLIER]
      ,[OLDSUPPLIER]
      ,[MARGIN]
      ,[COST_EST]
      ,[COST_ACT]
  FROM [MY_TABLE]
  ORDER BY [WKSHTNUM]
0
 
cdemott33Author Commented:
manishkungwani - Unfortunately that doesn't work.  It will returns all the rows in my table. Any other thoughts?

angelIII - I'm reading the link you've provided?

HainKurt: - I'm trying to return all rows where the WKSHTNUM is unique.  If I do something like this:

SELECT DISTINCT([WKSHTNUM])
  FROM [MY_TABLE]

I get 639 rows retured.  However if I do what manishkungwani suggested I get over 7000 records.  I want the 639 rows but I want it to include the additional columns.  Makes sense?
0
 
HainKurtSr. System AnalystCommented:
manishkungwani, what does

select distinct(col_name), other columns...

do? is it different than

select distinct col_name, other columns...
0
 
HainKurtSr. System AnalystCommented:
does not make sense :) you can only group by that column and use min/max/avg on oher columns... like this:

SELECT [WKSHTNUM],
      ,sum([COST_EST]) sum_est
      ,sum([COST_ACT]) sum_act
  FROM [MY_TABLE]
 group by [WKSHTNUM]
  ORDER BY [WKSHTNUM]

can you please post an excel sheet with a sample data and what do you want to get? to me, it looks like just a display issue in your app...
0
 
HainKurtSr. System AnalystCommented:
table
A 1 B
A 2 C
A 3 D
B 1 E
B 2 F

so, if you want just 2 records from here, what other columns will have? you want

A 1,2,3 B,C,D
B 1,2 E,F

join all values into one value???? or get first/last record based on a sort order

First rec grouped by col1, sorted by col2
A 1 B
B 1 E

Last rec grouped by col1, sorted by col2
A 3 D
B 2 F

you should really show what you want on a sample data...
0
 
8080_DiverCommented:
I want the 639 rows but I want it to include the additional columns.  Makes sense?

Honestly, no it doesn't make sense. ;-)

I know what you think you are saying but I am not sure you understand the implications of what you are saying.

Let's say that there are 43 rows with a given [WKSHTNUM] value (e.g. 5).  Now, you pull the Distinct value (5) and then you want the other X columns, right?  Well which row do you want the columns form?  After all there are 43 rows that probably have distinct combinations of values in the other X rows.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.