-1

I am trying to sort data in an SQL table and then perform query operations on the sorted data.

Usually the ORDER BY keyword sorts data from the resulting query. But how do I sort the data first, and then perform query operations on the data?

I tried the following query, which doesn't work.

SELECT NAME FROM (SELECT * FROM TABLE ORDER BY ID) WHERE <query_operation_after_sorting>;

Although the case here is to find the median, the approach I want to have here is to sort the data first and then perform any operations with it, without using any other functions.

Trikto
  • 3
  • 3
  • You'll have to explain what "operations" mean. It isn't a standard SQL lingo and I don't really know what you mean. – Álvaro González Mar 11 '23 at 13:49
  • @ÁlvaroGonzález I need to find the median from the dataset, and the question has been rephrased. – Trikto Mar 11 '23 at 13:53
  • Please tag your question with the DBMS you are using - but I guess if you want to find the median value of a dataset I would use the MEDIAN function – NickW Mar 11 '23 at 13:55
  • So you want to display median but also the individual values used to calculate it? I presume you want the median duplicated for every row (result sets needs to be, well, rectangular). You can use windowing functions for that, but not all database engines support them. – Álvaro González Mar 11 '23 at 13:59
  • @ÁlvaroGonzález I don't need duplications, I need to sort the data in ascending order and then find the mid value from that data. – Trikto Mar 11 '23 at 14:14
  • I would be very surprised if there aren't answers out there did you search for mysql median ? or come straight here. – P.Salmon Mar 11 '23 at 14:43
  • @P.Salmon Although the case here is to find the median, I looked for an approach where I can sort the data first and then perform operations with it, and not with any other functions. Anyway my question has been answered here. – Trikto Mar 11 '23 at 15:35
  • Median is an accumulation of individual values, prior order (if any) will be lost. I honestly don't get what you're trying to accomplish, sorry. Can you add an example to the question (input and output)? – Álvaro González Mar 11 '23 at 17:09

1 Answers1

-2

You can you use a CTE expression to ORDER your results before looking for the median. Something like this:

WITH ordered AS (
  SELECT NAME, ID 
  FROM TABLE
  ORDER BY ID
)
SELECT ordered.NAME, ordered.ID FROM ordered

You can query the intermediate result as it was a table (it is indeed).

For further reference check: What is a CTE ?

arthurq
  • 319
  • 1
  • 6
  • 2
    Please be aware that the answer doesn’t guarantee that the result of the final select is ordered - if that is what you are trying to achieve. Ordering is only effective within the (sub) statement that has the ORDER BY clause – NickW Mar 11 '23 at 15:50
  • 1
    Full query is equivalent to `SELECT NAME, ID FROM TABLE`. – Álvaro González Mar 11 '23 at 17:10