• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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

0
cdemott33
Asked:
cdemott33
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now