Query to Order by the Number of Rows Returned from Another Select

Query to ORDER BY the number of rows returned from another SELECT

The Postgres way to do this:

SELECT *
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS friends
   FROM   friends
   ) f USING (user_id)
ORDER  BY f.friends DESC NULLS LAST, user_id  -- as tiebreaker

• The keyword AS is just noise for table aliases. But don't omit it from column aliases. The manual on "Omitting the AS Key Word":

  In FROM items, both the standard and PostgreSQL allow AS to be omitted
  before an alias that is an unreserved keyword. But this is impractical
  for output column names, because of syntactic ambiguities.

  Bold emphasis mine.

• ISNULL() is a custom extension of MySQL or SQL Server. Postgres uses the SQL-standard function COALESCE(). But you don't need either here. Use the NULLS LAST clause instead, which is faster and cleaner. See:

  • PostgreSQL sort by datetime asc, null first?

• Multiple users will have the same number of friends. These peers would be sorted arbitrarily. Repeated execution might yield different sort order, which is typically not desirable. Add more expressions to ORDER BY as tiebreaker. Ultimately, the primary key resolves any remaining ambiguity.

• If the two tables share the same column name user_id (like they should) you can use the syntax shortcut USING in the join clause. Another standard SQL feature. Welcome side effect: user_id is only listed once in the output for SELECT *, as opposed to when joining with ON. Many clients wouldn't even accept duplicate column names in the output.

Should an ORDER BY ever affect the number of rows returned a SELECT query?

Since the order of operations in SQL is:

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

this does seem to be a bug indeed. Which DBMS is it?

Counting no of rows returned by a select query

SQL Server requires subqueries that you SELECT FROM or JOIN to have an alias.

Add an alias to your subquery (in this case x):

select COUNT(*) from
(
select m.Company_id
from Monitor as m
    inner join Monitor_Request as mr on mr.Company_ID=m.Company_id
    group by m.Company_id
    having COUNT(m.Monitor_id)>=5)  x

Using SELECT result in another SELECT

NewScores is an alias to Scores table - it looks like you can combine the queries as follows:

SELECT 
    ROW_NUMBER() OVER( ORDER BY NETT) AS Rank, 
    Name, 
    FlagImg, 
    Nett, 
    Rounds 
FROM (
    SELECT 
        Members.FirstName + ' ' + Members.LastName AS Name, 
        CASE 
            WHEN MenuCountry.ImgURL IS NULL THEN 
                '~/images/flags/ismygolf.png' 
            ELSE 
                MenuCountry.ImgURL 
        END AS FlagImg, 
        AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett, 
        COUNT(Score.ScoreID) AS Rounds 
    FROM 
        Members 
        INNER JOIN 
        Score NewScores
            ON Members.MemberID = NewScores.MemberID 
        LEFT OUTER JOIN MenuCountry 
            ON Members.Country = MenuCountry.ID 
    WHERE 
        Members.Status = 1 
        AND NewScores.InsertedDate >= DATEADD(mm, -3, GETDATE())
    GROUP BY 
        Members.FirstName + ' ' + Members.LastName, 
        MenuCountry.ImgURL
    ) AS Dertbl 
ORDER BY;

Performing a query on a result from another query?

Usually you can plug a Query's result (which is basically a table) as the FROM clause source
of another query, so something like this will be written:

SELECT COUNT(*), SUM(SUBQUERY.AGE) from
(
  SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age
  FROM availables
  INNER JOIN rooms
  ON availables.room_id=rooms.id
  WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009-06-25', INTERVAL 4 DAY) AND rooms.hostel_id = 5094
  GROUP BY availables.bookdate
) AS SUBQUERY

Stack query results in one query result so the order of rows depends on which part returns which result

You have 2 options:

1. Use a CASE expression to order by the filter condition - this works when the tables are the same and the conditions simple.

select a.col1, a.col2
from a
where a.col3 = 1 or a.col4 = 0
order by
  case when a.col3 = 1 then 1 else 0 end desc
  , a.col2;

As noted by dogyog, this where clause should use OR not AND to combine the 2 queries.

2. Use UNION ALL

select a.col1, a.col2, 0 QueryNum
from a
where a.col3 = 1

union all

select a.col1, a.col2, 1 QueryNum
from a
where a.col4 = 0

order by QueryNum asc, a.col2;

Execute SELECT for all returned rows from another SELECT within the same query

You should use OUTER APPLY or CROSS APPLY instead of a cursor:

SELECT *
FROM (  SELECT id 
        FROM org.employees 
        WHERE {some_condition}) A
OUTER APPLY org.work_schedule(A.id, @fromDate, @toDate) B

How to get total number of rows in a executed select statement?

You either have to use SELECT COUNT(*) ... with the same condition or add a column with the row-count via ROW_NUMBER function:

SELECT a.Emp,b.orders, RN = ROW_NUMBER () OVER (ORDER BY a.Emp,b.orders) 
FROM table as a inner join table1 b on a.ID=B.ID

...or use @@ROWCOUNT after the select.

Instead of ROW_NUMBER it's easier to use COUNT(*) OVER ( Order By ...) where each row contains the same total-count whereas ROW_NUMBER would return a sequential number where only the last record(acc. to the ORDER BY) would have the total-count.

So what Aaron has already meantioned in his answer.

How to sort result from one SQL query with data from other table

You can have it with the ID:

SELECT c.customer_name name
       , COUNT(order_id) sumarum
       , o.customer_id id
FROM Orders O
left join Customers C
on o.customer_id = c.customer_id
GROUP BY customer_name, o.customer_id
ORDER BY c.customer_name, o.customer_id;

Or without the ID (Just name and count):

select name
       , sumarum
from (SELECT o.customer_id id
       , COUNT(order_id) sumarum
       , c.customer_name name
FROM Orders O
left join Customers C
on o.customer_id = c.customer_id
GROUP BY customer_name, o.customer_id
ORDER BY c.customer_name, o.customer_id);

Here is the DEMO

---

Related Topics