Using the HAVING function

Transact-SQL
1

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.
Ex:
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.

2

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
FROM
(
	SELECT *,
		COUNT(*) OVER (PARTITION BY movenumber) AS N
	FROM
		OrderTable
) s WHERE N > 1
3

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

4

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.