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.