-2

I have an order table holding columns : 

ID
date
order_number
cost

In the table there are the following rows : 

1,TODAY,123,100
2,YESTERDAY, 123,150
3,TODAY, 144,20
4,TODAY, 144,20
5,TODAY, 144,200

(Notice that the 3rd column is the order number)

In the result set returned there should return :

1,TODAY,123,100
3,TODAY, 144,20
4,TODAY, 144,20

I am a poor SQL know-how (A web designer, go figure).

Fixed and found the answer

After searching and researching i found what i needed. I put all the records in a temporary table and run a cursor holding the order_number and the count of rows for the order_number like this :

declare cur cursor for
SELECT order_number,((COUNT(*) + 1) / 2) as counter from @tmpTbl group by order_number

Now i run over the cursor and insert into another table the top(counter) from the @tmpTbl into another table with the same structure (lets call it @slimTbl). So, we have this piece of code :

>fetch next from cur into @order_number,@counter
>
>while @@FETCH_STATUS = 0
>
>   begin
>
>     INSERT INTO @slimTbl (id,date,order_number,cost)
>
>            (SELECT TOP(@counter) id,date,order_number,cost FROM @tmpTbl
>             WHERE order_number = @order_number )
>
>     fetch next from cur into @order_number,@counter
>
>   end
>
>close cur 
>
>deallocate cur

I hope this is clear now. I thank you all for helping me... Feel free to contact me to see the full solution I created.. a little long but works like magic :)

Drew
  • 24,851
  • 10
  • 43
  • 78
Ofer
  • 11
  • 2
  • 2
    Which DBMS are you using? Postgres? Oracle? Btw: terms like ASAP will probably get you some down votes. –  Nov 19 '14 at 07:14
  • possible duplicate of [how to show only even or odd rows in sql server 2008?](http://stackoverflow.com/questions/2997458/how-to-show-only-even-or-odd-rows-in-sql-server-2008) – Ajay Nov 19 '14 at 07:17
  • Sorry for the "ASAP".. i am not familliar with the terms in here... – Ofer Nov 19 '14 at 08:27
  • I doubt you can do this in a single statement. You could filter every 2nd & 3th elementh or filter out the even ones. But when iterating the records sql doesn't know how many similar records will follow(it can know how many have passed though). eg while filtering the records with id 3 & 4 it can't know that id 5 has the same invoice ID. You could probably do it with a subquery that does a count first but based on your sql experience i wouldn't go that complex. – Kristof Nov 19 '14 at 11:52

1 Answers1

0

I agree with Ajay and a_horse_with_no_name but i happened to have the solution lying around for t-sql : You create a row number and do a remainder division of that to get the even or uneven rows.

SELECT ROW_NUMBER() over( order by number) % 3 as selector, * 
FROM yourTable
WHERE selector <= 1

edit2
Calculate row number per invoice number, do a remainder division by 3 and select everything that has a remainder of 1 or 0.
Data before where filter would look like this :

ID - InvoiceNr - RowNumber - Remainder
1 - ABC123__- 1 ________- 0
2 - ABC123__- 2________- 1
3 - ABC123__- 1________- 2
4 - FGH789__- 1________- 0

So records 1,2 and 4 would be selected because the remainder is <= 1

Edit
To clarify: ROW_Number creates a rownumber and resets at every differnt [number] in your table. it would look like this
ID - InvoiceNr - RowNumber
1 - ABC123__- 1
2 - ABC123__- 2
3 - CDE456__- 1
4 - FGH789__- 1
4 - FGH789__- 2

This allows you to filter on RowNumber and get only the first record of your invoice. Is this not what you wanted?

Kristof
  • 3,267
  • 1
  • 20
  • 30
  • Thanks for that, but i need to get rows not by row number but by id – Ofer Nov 19 '14 at 08:27
  • Edited my reply, i hope that clears things up because i don't really get what you mean by : you want to get rows by id – Kristof Nov 19 '14 at 08:52
  • I want to retrieve rows by invoiceNr, but if there are 3 rows,it will return the first 2 (if 4, than 2 also)... Thanks again – Ofer Nov 19 '14 at 09:10
  • Maybe provide a set of data and the data you expect back because i'm still not following what you actually want. – Kristof Nov 19 '14 at 09:12
  • You said " I want to select rows but for every 2 orders for the same "order_number" , to fetch only 1." in your question. But now you want the first 2? I'm Still confused :) – Kristof Nov 19 '14 at 09:13
  • For the table in my question. i have several orders with the same order number... i need to reffer to every 2 rows with the same order number as 1 row and get the first one... so if i have 3 rows with order number 5, the result will be the first 2 rows, and if i have 2 rows, it will be the first row. Hope i could explain myself better... – Ofer Nov 19 '14 at 09:27
  • Not at all :s i'm even more confused now. Imagine InvoiceNr in my example is order_number. Which rows would have to be returned? or if my sample data is not enough edit your question create your example there(i feel that the comment window isn't big enough for a clear demo :) ) – Kristof Nov 19 '14 at 09:36
  • hold on ! moment of clarity :) – Kristof Nov 19 '14 at 09:37