mersenneforum.org  

Go Back   mersenneforum.org > Extra Stuff > Programming

Reply
 
Thread Tools
Old 2017-05-13, 15:16   #1
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

11101110100002 Posts
Default SQL puzzle

Using SQLite: https://www.sqlite.org/lang.html

Given a table with two columns a, b is it possible to create a query that returns the largest 3 values of b for each a?

I've tried using WITH and/or LIMIT without success. I thought this would work:
Code:
select y.* from (select distinct a as d from abc) as x, (select * from abc where a=d order by b desc limit 3) as y;
but I get "Error: no such column: d"
Prime95 is offline   Reply With Quote
Old 2017-05-13, 16:01   #2
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

24·32·53 Posts
Default

Nuts. A half hour after posting, I've got a solution:

Code:
select * from abc d where rowid in (select rowid from abc e where e.a=d.a order by b desc limit 3);
Prime95 is offline   Reply With Quote
Reply

Thread Tools


Similar Threads
Thread Thread Starter Forum Replies Last Post
Some puzzle Harrywill Puzzles 4 2017-05-03 05:10
Four 1's puzzle Uncwilly Puzzles 75 2012-06-12 13:42
4 4s puzzle henryzz Puzzles 4 2007-09-23 07:31
Dot puzzle nibble4bits Puzzles 37 2006-02-27 09:35
now HERE'S a puzzle. Orgasmic Troll Puzzles 6 2005-12-08 07:19

All times are UTC. The time now is 16:32.


Tue Oct 19 16:32:55 UTC 2021 up 88 days, 11:01, 0 users, load averages: 1.51, 1.44, 1.39

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.

This forum has received and complied with 0 (zero) government requests for information.

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation.
A copy of the license is included in the FAQ.