CODEWITHSUNDEEP
sqlsql-servert-sql
user1800552
user1800552

Reputation: 372

Simple max() function and inner join T-SQL

I have simple table which puzzles me:

CREATE TABLE [dbo].[T3]
(
   [id] [int] IDENTITY(1,1) NOT NULL,
   [S_Id] [int] NULL,
   [P_Id] [int] NULL,
   [level] [int] NULL,
   [Path] [nvarchar](255) NULL
) ON [PRIMARY]

In the table is data

id  S_Id    P_Id    level   Path
------------------------------------
1   218252  218231  1   218231
2   218271  218252  1   218252
3   218271  218252  2   218231-218252

EDIT:

I try to get the 

ID, S_ID, P_ID, level, Path

on maximum length of column Path.

It should return id 3.

If I try to get max len from path like this:

select 
    b.id, a.p_id, a.s_id,
    max(len(a.path)) as Path, 
    a.path  
from 
    t3 a, t3 b
where 
    b.id = a.id
group by 
    a.p_id , a.s_id, b.id , a.path
order by 
    1

I get all the data, not just row with id 3, why ?

Upvotes: 1

Views: 225

Answers (3)

zhongxiao37
zhongxiao37

Reputation: 987

If you only want the max path record... Correct me if I'm wrong.

;WITH tmp AS (select TOP 1 id from #TaskTask3 ORDER BY LEN(path) DESC)
select t.*
from #TaskTask3 t 
inner join tmp on tmp.id = t.id

Updates

;WITH tmp AS (select id, row_number() over (partition by S_Id, P_Id order by len(path) DESC) as rn from #TaskTask3)
select t.*
from #TaskTask3 t 
inner join tmp on tmp.id = t.id 
WHERE tmp.rn = 1

Upvotes: 1

Raghavi Kannan
Raghavi Kannan

Reputation: 67

To put weePee's answer in a simple way,you can use the following query:

select id,s_id,p_id,level  from t3 where len(path)= (select max(len(path)) from t3)

This is what I used to create and insert into table t3:

CREATE TABLE [dbo].[T3]
(
   [id] [int] IDENTITY(1,1) NOT NULL,
   [S_Id] [int] NULL,
   [P_Id] [int] NULL,
   [level] [int] NULL,
   [Path] [nvarchar](255) NULL
) ON [PRIMARY]
insert into t3 (s_id,p_id,level,path) values (218252,218231,1,'218231'),(218271,218252,1,'218252'),(218271,218252,2,'218231-218252')

Upvotes: 0

weePee
weePee

Reputation: 905

I tried to keep it simple....There are other methods (mentioned already) but I think you need to start slow... :)

declare @maxLen int

select @maxLen =  max(len(path))
    from t3 

select * from t3 
where len (path) = @maxLen

Upvotes: 0

Related Questions