filtering result

Question

Post reply

filtering result

fawwad

SSC Eights!

Points: 965
More actions
December 13, 2010 at 8:33 am

#232456

Hi all,
There was some requirement from my client and i give them the required information.
but here i want to make it more optimize...
if you look at below data, status repeating, i want to filter these repeating status...
what i want is this
startup
Ignition off
startup
Ignition off
startup
and remove the repeating rows...
ANZ 953Startup12/2/2010 17:47
ANZ 953Ignition Off12/2/2010 17:47
ANZ 953Ignition Off12/2/2010 17:47
ANZ 953Startup12/2/2010 17:47
ANZ 953Startup12/2/2010 17:47
ANZ 953Ignition Off12/2/2010 17:48
ANZ 953Ignition Off12/2/2010 17:48
ANZ 953Ignition Off12/2/2010 17:48
ANZ 953Ignition Off12/2/2010 17:48
ANZ 953Ignition Off12/2/2010 17:48
ANZ 953Ignition Off12/2/2010 17:48
ANZ 953Ignition Off12/2/2010 17:48
ANZ 953Ignition Off12/2/2010 17:48
ANZ 953Ignition Off12/2/2010 17:49
ANZ 953Startup12/2/2010 17:49
ANZ 953Startup12/2/2010 17:49
ANZ 953Startup12/2/2010 17:49
ANZ 953Ignition Off12/2/2010 17:49
ANZ 953Ignition Off12/2/2010 17:49
ANZ 953Ignition Off12/2/2010 17:49
ANZ 953Ignition Off12/2/2010 17:49
ANZ 953Startup12/2/2010 17:49
ANZ 953Ignition Off12/2/2010 17:49
ANZ 953Ignition Off12/2/2010 17:50
ANZ 953Ignition Off12/2/2010 17:50
please help me out here...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 737686 More actions · Answer 1

What does filter mean? Are you looking to return this in a report or delete the data?

If duplicates, the distinct keyword can help you in a query to only show unique rows.

Sean Lange SSC Guru Points: 286602 More actions · Answer 2

check out "distinct" keyword. Another option would be using the Group By clause.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

fawwad SSC Eights! Points: 965 More actions · Answer 3

No, i just sent you piece of my result set.

i retrive this from a query...

select p.id as 'CarID',p.cellnumber as 'Reporting SIM',p.Longitude,p.Latitude,p.statustext as 'Status',

p.assembled as 'Sent Time',p.received as 'Received Time',p.Distance,p.Direction,p.place as 'Location' from mobdat m

inner join poslog p on p.cellnumber = m.cellnumber

inner join groups g on m.nodenumber = g.nodenumber

where g.groupnumbe = 78075 and p.received between '2010-12-09 00:00:01' and '2010-12-09 23:59:59' and

p.statustext in ('startup','ignition off')

order by p.received

now, i have to alter this query to get my desired result as i said earlier.

i think now it better make sense to you guys

Sean Lange SSC Guru Points: 286602 More actions · Answer 4

fawwad (12/13/2010)
No, i just sent you piece of my result set.
i retrive this from a query...
select p.id as 'CarID',p.cellnumber as 'Reporting SIM',p.Longitude,p.Latitude,p.statustext as 'Status',
p.assembled as 'Sent Time',p.received as 'Received Time',p.Distance,p.Direction,p.place as 'Location' from mobdat m
inner join poslog p on p.cellnumber = m.cellnumber
inner join groups g on m.nodenumber = g.nodenumber
where g.groupnumbe = 78075 and p.received between '2010-12-09 00:00:01' and '2010-12-09 23:59:59' and
p.statustext in ('startup','ignition off')
order by p.received
now, i have to alter this query to get my desired result as i said earlier.
i think now it better make sense to you guys

So Just add distinct to your list.

select DISTINCT p.id as 'CarID',p.cellnumber as 'Reporting SIM',p.Longitude,p.Latitude,p.statustext as 'Status',

p.assembled as 'Sent Time',p.received as 'Received Time',p.Distance,p.Direction,p.place as 'Location' from mobdat m

inner join poslog p on p.cellnumber = m.cellnumber

inner join groups g on m.nodenumber = g.nodenumber

where g.groupnumbe = 78075 and p.received between '2010-12-09 00:00:01' and '2010-12-09 23:59:59' and

p.statustext in ('startup','ignition off')

order by p.received

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

fawwad SSC Eights! Points: 965 More actions · Answer 5

yes thanks , understand

it will resolve some of my problem but what in this case

ARQ 8369.23028E+1171.57336734.01695Ignition Off12/1/2010 13:00

ARQ 8369.23028E+1171.57336734.01695Ignition Off12/1/2010 13:01

ARQ 8369.23028E+1171.57336734.016967Ignition Off12/1/2010 13:01

here distinct will remove some of the records but still i will have couple of ignition off

i want one ignition off and then next Startup , ignition off and startup

just one instant for these status, dont want any repeat

how can we achieve this ...

Sean Lange SSC Guru Points: 286602 More actions · Answer 6

I guess the question is why is a field like latitude included if the value is not relevant? You either need to leave it in the result with all the values, pick one of the values in each grouping, or remove it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

fawwad SSC Eights! Points: 965 More actions · Answer 7

well lat,long is important too.

reason i want to filter on status is this, i am receiving data too frequently and it isnt useful for client.

it is just making report to go longer.

sometimes getting 3 records per second, then 2 more on next second while status doesnt change !!

so, it is not meaningful for them.

can we sort it out ?

Sean Lange SSC Guru Points: 286602 More actions · Answer 8

I guess what i was saying is that if lat and long are important then you will need to include them like you already are. You will either need to leave this data off the report of live with the report running long.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

fawwad SSC Eights! Points: 965 More actions · Answer 9

fawwad

SSC Eights!

Points: 965

December 13, 2010 at 10:14 am

#1261825

humm understand

thanks

filtering result

Cookies on SQLServerCentral