?
Solved

SQL DISTINCT not working

Posted on 2013-12-05
8
Medium Priority
?
309 Views
Last Modified: 2014-01-30
I have a query that I'm added ROW_NUMBER() to it, but when I try to add DISTINCT or GROUP BY it does not work.

Here is the query.

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY fpacode) AS Rownumber, fpaCode, listname, fpaID, emailTo, status, cfid FROM fpaBasket WHERE cfid = 'D1W12978461') AS Sample WHERE Rownumber < 11

Open in new window

0
Comment
Question by:Medrise
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:Medrise
ID: 39698241
I need to group all fpaCode
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1500 total points
ID: 39698249
ROWNUMBER adds a 1, 2, 3, ... to all rows.

Which means, the rows would by definition all become DISTINCT, which defeats the purpose of having DISTINCT in your query.

>I need to group all fpaCode
So if a specific ROWNUNBER has 4, 5, and 6 for fpaCode = 1, then which ROWNUMBER do you want returned in the single row for fpaCode = 1?
0
 

Author Comment

by:Medrise
ID: 39698266
I want to add the ROW_NUMBER to this query, so should do a SELECT with ROW_NUMBER() with this query?

SELECT DISTINCT(fpaCode), listname, fpaID, emailTo, status, cfid FROM fpaBasket WHERE cfid = 'D1W12978461'

Open in new window

0
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1500 total points
ID: 39698291
Please answer this question:
>I need to group all fpaCode
So if a specific ROWNUNBER has 4, 5, and 6 for fpaCode = 1, then which ROWNUMBER do you want returned in the single row for fpaCode = 1?
0
 

Author Comment

by:Medrise
ID: 39698350
I can return ROWNUMBER 4 that would be fine...
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1500 total points
ID: 39698473
So to clarify, you want to return the minimum ROWNUMBER value for a given grouping of fpaCode?

Example:

fpaCode  rownumber
1              1                  --say there are three rows with fpaCode = 1, but only one row is displayed here
2              4                  --say there are 13 rows with fpaCode = 2, but only one row is displayed here
3              17
0
 
LVL 32

Expert Comment

by:awking00
ID: 39698558
Can you provide some sample data and your expected output?
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39700339
If you want to "group all fpaCode" why are you using row_number()?
Do you mean something like this perhaps?
This would give you one row per fpaCode by using "partition by fpaCode"
SELECT TOP 10
      *
FROM (
            SELECT
                  ROW_NUMBER() OVER (PARTITION BY fpaCode ORDER BY cfid) AS Rownumber
                , fpaCode
                , listname
                , fpaID
                , emailTo
                , status
                , cfid
            FROM fpaBasket
            WHERE cfid = 'D1W12978461'
      ) AS Sample
WHERE Rownumber = 1

Open in new window

Note while I have chosen to "order by cfid" but that is purely an arbitrary choice.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question