Selecting Values from a table with exlusions

Question

0.00/5 (No votes)

See more:

Hi Guys

I currently have a view in my database which Selects a count of a product

SQL

SELECT     TOP (100) PERCENT COUNT(ProductSerial) AS cnt, ProductSerial
FROM         dbo.LIstAllStages

GROUP BY ProductSerial
HAVING      (COUNT(ProductSerial) > 1)
ORDER BY ProductSerial

it returns the product serial with a count.

what i need to do now is create a new view.
that would show all the productserials from the above view which would exclude the first date that it was in the listallstages table

how could i possibly do this?

Posted 27-Feb-13 23:08pm

isi19

Add a Solution

Comments

Babu Kumar 28-Feb-13 5:33am

Hi,
I Couldn't understand your problem clearly, Kindly give more info to understand your problem.

Regards,
Babu.K

isi19 28-Feb-13 6:00am

I have a View called listallstages , which captures dates which the productSerial passes through the system.

what i want to do is select all the records which have a count > 1 , but it should only show the records from the second date fround, so if the productserial is 600p

the listallstages view will show
ProductSerial Date
600p 27/02/2013
600p 28/02/2013
600p 1/03/2013
the query that i have posted will show
cnt productserial
3 600p

in my query i only want to show the records with a cnt > 1 from the above view
join it onto the listallstages , and show all the dates excluding the first date

so it would only show

600p 28/02/2013
600p 1/03/2013

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hrushikesh_phapale · Accepted Answer · 2013-02-28T19:53:00

Solution 1

I think this is what you exactly wants,

SQL

WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY ProductSerial
                                       ORDER BY ( SELECT 0)) RN,ProductSerial,[Date]
         FROM   dbo.LIstAllStages)
Select ProductSerial,[Date] FROM cte
WHERE  RN > 1

hopes this solve your problem

Posted 28-Feb-13 19:53pm

Hrushikesh_phapale

Comments

isi19 4-Mar-13 3:54am

Thanks yes this is exactly what i needed