1

In SSMS we have a view, which is returning distinct columns by the following command,

Create View [VIEWNAME] As
`Select distinct [Col1],[Col2], Max(TimeDate) as TimeDate
 from [Table]
  Group By [Col1],[Col2]`

I want the column [Col3] as well from the table in view.

I tried the following so far but unfortunately, it didn't work for me

Select Distint on [Col1],[Col2] * from [Table]

Error: Incorrect syntax near 'on'.

Also,

select [Col1],[Col2],[Col3],Max[TimeDate] from [Table]
Group by [Col1],[Col2],[Col3],[TimeDate]

Error: Column TABLE.Col3 is invalid in the select list because it is not contained in either an aggregated function or GROUP BY clause.

Below is the original table sample.

enter image description here

Desired table image

enter image description here Thanks.

Eva
  • 11
  • 2
  • 1
    Please add sample data to your question. – Tim Biegeleisen Oct 24 '21 at 09:04
  • 1
    Please tag your RDBMS. – Stefanov.sm Oct 24 '21 at 09:04
  • Done @Stefanov.sm – Eva Oct 24 '21 at 09:27
  • Added Images of original and desired table @TimBiegeleisen – Eva Oct 24 '21 at 09:29
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Oct 24 '21 at 09:30
  • Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL include DDL & tabular initialization code. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. [ask] [Help] – philipxy Oct 24 '21 at 09:31

1 Answers1

0

You can use distinct on in Postgresql.

select distinct on (col1, col2) *
from the_table
order by col1, col2, timedate desc;

I guess that your RDBMS is SQL Server. The query below uses the SQL server equivalent of Postgresql distinct on ().

select col1, col2, col3, timedate
from
(
 select *, row_number() over (partition by col1,col2 order by timedate desc) as rn 
 from the_table
) as t
where rn = 1;
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • Thankyou so much @Stefanov.sm – Eva Oct 24 '21 at 15:26
  • Just a quick question! Can we delete rows in view? I deleted it from the table but still shown in the view. Maybe the view turns to a complex view. – Eva Oct 24 '21 at 19:55
  • Accepted the answer already. It will count once my reputation reaches 15(as I am a new user) but the feedback has been recorded. Thanks – Eva Oct 24 '21 at 20:00
  • [Here](https://stackoverflow.com/questions/33778255/msql-delete-rows-from-view) is a good SO thread on deleting rows from a view. – Stefanov.sm Oct 24 '21 at 21:51