sql server - How to use group by only for some columns in sql Query? -

the following query returns 550 records, grouping columns in controller via linq. however, how can achieve "group by" logic in sql query itself? additionally, post-grouping, need show 150 results user.

current sql query:

select distinct    l.id loadid   , l.loadtrackingnumber loaddisplayid   , planningtype.text planningtype   , loadstatus.id statusid   , loadworkrequest.id loadrequestid   , loadstatus.text status   , routeids.routeidentifier routename   , planrequest.id planid    , originpartyrole.id originid   , originparty.id originpartyid   , originparty.legalname origin   , destinationpartyrole.id destinationid   , destinationparty.id destinationpartyid   , destinationparty.legalname destination   , coalesce(firstsegmentlocation.window_start, originlocation.window_start) startdate   , coalesce(firstsegmentlocation.window_start, originlocation.window_start) begindate   , destlocation.window_finish enddate     number     domain.loads (nolock) l     inner join dbo.lists (nolock) loadstatus on l.loadstatusid = loadstatus.id     inner join domain.routes (nolock) routeids on routeids.id = l.routeid      inner join domain.baserequests (nolock) loadworkrequest on loadworkrequest.loadid = l.id      inner join domain.baserequests (nolock) planrequest on planrequest.id = loadworkrequest.parentworkrequestid     inner join domain.schedules planschedule on planschedule.id = planrequest.scheduleid      inner join domain.segments (nolock) os on os.routeid = routeids.id , os.[order] = 0     inner join domain.locationdetails (nolock) originlocation on originlocation.id = os.destinationid     inner join dbo.entityroles (nolock) originpartyrole on originpartyrole.id = originlocation.dockroleid     inner join dbo.entities (nolock) originparty on originparty.id = originpartyrole.partyid      inner join domain.locationdetails (nolock)  destlocation on destlocation.id = routeids.destinationfacilitylocationid       inner join dbo.entityroles (nolock)  destinationpartyrole on destinationpartyrole.id = destlocation.dockroleid     inner join dbo.entities (nolock)  destinationparty on destinationparty.id = destinationpartyrole.partyid      inner join dbo.transportationmodes (nolock) lictm on lictm.id = l.loadinstancecarriermodeid     inner join dbo.entityroles  (nolock) carrierpartyrole on lictm.carrierid = carrierpartyrole.id      inner join dbo.entities  (nolock) carrier on carrierpartyrole.partyid = carrier.id       inner join dbo.entityroles  (nolock) resppartyrole on l.responsiblepartyid = resppartyrole.id      inner join dbo.entities  (nolock) respparty on resppartyrole.partyid = respparty.id      inner join domain.loadorders (nolock) lo on lo.loadinstanceid = l.id      inner join domain.orders  (nolock) o on lo.orderinstanceid = o.id      inner join domain.baserequests  (nolock) loadrequest on loadrequest.loadid = l.id       --load start date     left join domain.segments (nolock) segment on segment.routeid = l.routeid , segment.[order] = 0     left join domain.locationdetails (nolock) firstsegmentlocation on firstsegmentlocation.id = segment.destinationid      left join dbo.lists (nolock) planningtype on l.planningtypeid = planningtype.id      left join dbo.entityroles (nolock)  billtorole on o.billtoid = billtorole.id     left join dbo.entities  (nolock) billto on billtorole.partyid = billto.id                        o.customerid in (34236) , originlocation.window_start >= '07/19/2015 00:00:00' , originlocation.window_start < '07/25/2015 23:59:59' , l.ishistoricalload = 0 , loadstatus.id in (285, 286,289,611,290) , loadworkrequest.parentworkrequestid not null , routeids.routeidentifier not null , (planschedule.enddate null or (planschedule.enddate not null , cast(convert(varchar(10), planschedule.enddate,101) datetime) > cast(convert(varchar(10),getdate(),101) datetime))) order l.id desc 

linq:

//get custom grouped data var loadrequest = (from lq in returnlist                         let loaddisplayid = lq.loaddisplayid                         let origin = lq.originid //get  origin route                         let destination = lq.destinationid // destination route                    group lq new                    {                        routeid = lq.routename,                        planid = lq.planid,                        origin = lq.originid,                        destination = lq.destinationid                    }                    grp                    select new                    {                        routeid = grp.key.routeid,                        planid = grp.key.planid,                        origin = grp.key.origin,                        destination = grp.key.destination,                        loads = (from l in grp select l)                    }).orderby(x => x.origin).tolist(); 

i'm guessing want group column 1 include columns 2 , 3 in select. using group cannot this. however, can using t-sql windowing function using over() operator. since don't how want aggregate, cannot provide example. @ t-sql windowing functions. article might started.