Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL select problem

Posted on 2009-04-21
9
Medium Priority
?
211 Views
Last Modified: 2012-05-06
Hello,

I have a table with 3 columns :  tableID, passNumber and LineNo

There are some rows that have the same tableId and the same Passnumber but a different LineNo.

I know it because if I do a select distinct tableId, passNumber   I receive less rows in total...

I need to know wich one are duplicated because its an error to have both tableId and passnumber with a different LineNo So I have to check wich one I will keep.

any idea how to get only those duplicated?
0
Comment
Question by:PhilippeRenaud
  • 4
  • 3
  • 2
9 Comments
 
LVL 5

Accepted Solution

by:
bprojoe earned 1200 total points
ID: 24195413
select tableid, passnumber
from yourtable
group by tableid, passnumber
having count(*) > 1
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 24195497
Seems to work, but how come if I do  select count(*) from myTable  it returns: 6795

if I do : select distinct tableID, passNumber from myTable   :  the total is: 6322

and your code returns me:  232 rows.     (6795 - 6322 = 473)      473 different of  232 ..


am i doing an error ?
0
 
LVL 5

Expert Comment

by:bprojoe
ID: 24195606
doing..
select count(*) From yourtable group by tableid, passnumber
should give you the same row count as doing distinct.  the group by is the key here.
the query i gave you only returns the distinct/group by of tableid and passnumber when there are more than one record of those that are the same.
0
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.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24195614
PhilippeRenaud said:
>>am i doing an error ?

Nope.  There are 232 combinatons of tbaleID / passNumber that appear >1 time.  Of those, at least 1 appears
at least 3 times.
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 24195682
so what is the 473 then ?
0
 
LVL 5

Expert Comment

by:bprojoe
ID: 24195686
thanks for the correction.
0
 
LVL 5

Expert Comment

by:bprojoe
ID: 24195721
when running your distinct it includes ones that don't have duplicates along with the duplicates.
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 total points
ID: 24195753
PhilippeRenaud said:
>>so what is the 473 then ?

473 apparently is the number of times some combination or another got repeated
0
 
LVL 1

Author Closing Comment

by:PhilippeRenaud
ID: 31572812
Perfect, thank you.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

577 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