Lucid: Your problem is not.

Vancamp: Lucid: This is a misunderstanding of how GROUP BY works.

Balistrieri: Lucid: GROUP BY grp will return one and only one row per group.

Bolk: Doesn’t it just collapse on the specified column and allow aggregate functions to operate within those collapsed records?

Baumgarn: Lucid: Yes, it operated on the rows prior to the LIMIT.

Kusak: Lucid: Your LIMIT does not do what you want.

Burtt: This whole excercise is making me realize how much I dont know and its borderline overwhelming

Missler: Lucid: All your LIMIT does is show only 2 of the group results, not limit the rows within each group to process.

Gallishaw: Xgc: yeah thats what I want pretty much

Fouhy: Lucid: You don’t want that at all.

Wettlaufer: Xgc: I want the last 2 entries of a given seller, one is the current and one is the last?

Manzo: Lucid: You GROUP BY seller. There’s only one row per seller produced by GROUP BY.

Strong: Lucid: That’s where window functions become useful.

Garnett: Oh right limit is after; damn.

Gerken: Checking your earlier paste, I wanted to understand what I was doing wrong

Sanes: Lucid: I’m finding the last / top 5 per group in this particular problem.

Toca: I’ll be perfectly honest, I dont know what this is doing

Edde: In particular the cnt value is weird

Thomases: There’s a lot of other stuff happening here

Penzero: Lucid: With window functions, it’s much simpler: SELECT v1.* FROM SELECT r.*, ROW_NUMBER OVER PARTITION BY sellerid ORDER BY date DESC AS n FROM ranks AS r AS v1 WHERE v1.n = 2; Now you have the last 2 rows per seller in the result.

Igus: Why is cnt 2 for pKey 6 and 3 for pKey 5?

Pedraja: Isnt cnt based upon rating count? if so, shouldnt all ratings w/ 58 be cnt=3?

Meanor: Lucid: Based on the ORDER and the way I break ties with the primary key

Torralva: ON t1.rating, t1.pKey = t2.rating, t2.pKey

Hauxwell: Lucid: With this, the same ratings do not have the same rank, since the primary keys are part of the logic when ordering.

Keiter: That’s a more compact form – you can use ON t1.rating = t2.rating AND t1.pKey = t2.pKey

Plata: Keiter: Not exactly. You missed some logic.

Biggerstaff: It’s a weighted compare.

Keiter: Oh, I see your example now

Nurse: ON t1.rating = t2.rating OR t1.rating = t2.rating AND t1.pKey = t2.pKey

Mam: Sorry. It’s a little different with the =

Lapere: ON t1.rating t2.rating OR t1.rating = t2.rating AND t1.pKey = t2.pKey

Sandrowicz: Xgc: don’t take this the wrong way, I’m sure this is awesome and does what you want but it’s too complicated compared to what I am trying to do for me to reason about it

Town: Lucid: It’s just an FYI. Without window functions, the problem just gets messy.

Yocius: Xgc: oh sure, but this problem is also relatively complex

Joosten: Lucid: Your approach is terribly broken. You have to start with something that’s logically correct before worrying about how nice it looks or how simple it looks.

Hemmes: Xgc: that’s not a problem with my approach

Prayer: Lucid: No. It’s exactly the same, but the number of groups is 5, not 2.

Nicol: Number of top N is 5, not 2.

Kummer: Xgc: it’s a problem with my understanding of the individual pieces required to make it happen

Tinkham: Xgc: it also has the count value and this ordered PK stuff

Loverdi: Lucid: That’s true. It’s simple logic, but difficult to understand with limited SQL knowledge. I know that.

Nimon: Xgc: I’m a noob, I dont know enough about this so the added complexity is burdensome

Keiter: Lucid: so you prefer an incorrect approach?

Rochford: Lucid: Your problem is not as simple as you think. That’s part of your problem. You don’t realize some of the complexity.