sql select

A ScalikeJdbc SQL SELECT example with WrappedResultSet

Here’s another Scala ScalikeJdbc SQL SELECT example. A few unique things about this example is that it shows the JDBC-style parameters; it shows how to use a case class and the “service” style approach; and it shows how to use the WrappedResultSet as shown. Here’s the code:

A simple ScalikeJdbc SQL SELECT example

I don’t have time to write about ScalikeJdbc right now, so I’ll just say that it’s been working well for me. Here’s one of the first test examples I created, a simple SQL SELECT query that prints some output from a database table:

A SQL select group by, order by, and count query

As a note to self, this is how I wrote a “group by” and “order by” SQL query that gives me the number of times each country code occurs in a database table named url_clicks:

select country, count(1) as the_count
from url_clicks
where url_id=6
and country != ''
group by country
order by the_count desc

Results of this query look like this:

US, 50
CA, 30
GB, 15

That tells me that 50 people in the US clicked on the link with the id=6, 30 people from CA clicked on the same link, and 15 people from GB clicked on that link as well.

Play Framework: An Anorm ‘singleOpt’ query example

It’s embarrassing how long it took to get this to work, but if you ever need to select a single row from a database using Play Framework (version 2.6), Anorm, and singleOpt, I hope this example is helpful, and will save you time and frustration:

How to use SQL SELECT, GROUP BY, ORDER BY, and COUNT (all in one)

Don’t tell anyone, but my SQL skills are pretty average these days, at best, mostly because I haven’t had to do anything hard in a while. But just now I was happy to write this little SQL SELECT query that does a GROUP BY, an ORDER BY, and a COUNT, yielding the results shown in the image:

select nid, count(nid) from term_node
where tid in (3,1,11,10,9,8,7)
group by nid
order by count(nid) DESC

I’m going to use this query — or one very similar to it — to get a list of nodes (nid) that have the most tag ids (tid) from the list of tid in the query. In theory, the nodes (blog posts) that have the most tags in common should be the most related to each other. So, in my Scrupal6 replacement for Drupal 6, this query is a way to get “related” content for a given blog post. (The tid list shown comes from node id 4, so I need to also exclude nid=4 from the results. I also need to add a limit clause to the query.)

If you ever need to do a group by, order by, and count in one SQL query, I hope this example is helpful.