2

A question about how to get the data for each record that is the max in some GROUP appears over and over again on the net. There are many solutions, some of them easier to conceptualize than others. Does the query 'template' here have a name? I ask because one of the other patterns has the name 'correlated subquery' I believe. I have a need to issue this type of query often, and if I had names for the approaches I would I have a better mental index of the possible solutions to try.

Here's another example of the query type I want to know a name for.

Community
  • 1
  • 1
David Mann
  • 1,874
  • 2
  • 16
  • 19
  • These are not even all of the ways to do it. You can also use an exists. I have never seen names for the different types of queries, doesn't mean they don't exist. However, I doubt that they each have names because just by tweaking the queries a little bit you can give them a whole different meaning. Also, there are so many different subtleties that do the same thing. It's more about exploring what keywords are going to give you the best results based on what your specific query needs to do. – Jenn Jun 25 '14 at 16:27
  • @Jenn I completely agree. I find some of the types of solutions easier to understand and at a more basic level of SQL functionality. I think because the 'correlated subquery' approach has a name, I want the others to have simple [adjective]-[noun] names as well, or atleast the one I mention :) – David Mann Jun 25 '14 at 16:34
  • I think that it is a great question and I am curious myself. I just think that it is more likely that people have their own names for these different things, rather than a widely accepted name. When I was a kid, I would name fireworks. My favorite was one that I called 'The Weeping Willow'. You probably have an idea for which firework that is, but does it have a real name? I anticipate the answers being much like that. – Jenn Jun 25 '14 at 16:43
  • @Jenn For sure. I looked out at my back yard last night and saw lots of fireflies. Maybe you know them as lightning bugs, right? For some things there are a group of common terms. I just want at least one term thats as simple as "correlated subquery". I learned that the type of problem this query aims to solve has the common name "greatest n per group", so thats a great start for googling. – David Mann Jun 25 '14 at 18:58

1 Answers1

0

I would call that an example of a query with a compound join.

ryan
  • 188
  • 2
  • 7
  • What does compound mean in this sense? Is it because it has a < sign that may match N rows instead of just one? – David Mann Jun 25 '14 at 16:11
  • Yes, compound because there is more than one condition that joins the two tables together. Like your other conversation, I don't know if this is the formal name for it, but in googling "sql with compound join" several similar examples are found. – ryan Jun 26 '14 at 12:20
  • I suppose. That doesn't really inform one very much about what the approach is e.g. left outer join. Its also an example of a query with a where clause. After a few days of no more suggestions I'll accept yours..unless the name LOJEN (left outer join exclude nulls) takes over! – David Mann Jun 26 '14 at 20:09