SQLTeam.com | Weblogs | Forums

Using the HAVING function



Hey all,
I'm trying to pull a list of records from a table with ORder infomation. Say our table consists of 2 columns.From the example data below, i want to return all records where there's more than 1 ORderNumbr on a single MoveNumber.
MoveNumber OrderNumber
111222 98758
111222 90491
111222 98482
111223 84383

I need to return the first 3 records...

I tried running this script but it's not returning any results:

select movenumber, ordernumber from OrderTable
group by movenumber, ordernumber
HAVING COUNT(ordernumber)>1

It's fine if it returns the same movenumber 3 times; I just want figure out the logic of how to get this to work as i;m using it for a sub-report on an SSRS report.


Grouping by both movenumber and ordernumber would give you counts as 1 in the example data you have. What you would need to do is something like this:

SELECT movenumber, ordernumber
		COUNT(*) OVER (PARTITION BY movenumber) AS N
) s WHERE N > 1


That seemed to work! Never used OVER before, I'll read up on that now :stuck_out_tongue:
Thank you good sir!


Can you get two movements on the SAME Order Number?

If they were the only movements would you still include that?

If not then your query would need a bit of adjustment.