# Query help - distinct rows

**URL:** <https://forum.inductiveautomation.com/t/query-help-distinct-rows/3140>\
**Category:** Ignition\
**Created:** [July 28, 2011, 9:30pm UTC](https://forum.inductiveautomation.com/t/query-help-distinct-rows/3140 "2011-07-28T21:30:54Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [July 28, 2011, 9:30pm UTC](https://forum.inductiveautomation.com/t/query-help-distinct-rows/3140/1 "2011-07-28T21:30:54Z")

</div>

The query below returns 6 rows. LockOutPointID is returned as

[ul]  
L1-E2  
L1-E2  
L1-E2  
L1-E2  
L1-E4  
L1-E4  
[/ul]

I would like to return only distinct rows, however, the image column cannot be in the query if I use the distinct keyword. Any suggestions on how to do this?

`SELECT 
   LockOutPoints.LockOutPointID, 
   LockOutPoints.LockOutName, 
   LockOutPoints.LockOutPointLocation, 
   LockOutPoints.LockOutDevice
   LockOutPoints.LockOutImage
FROM LockOutEnergySources 
JOIN 
   LockOutPoints ON LockOutPoints.LockOutPointID = LockOutEnergySources.LockoutPoint
WHERE LockOutEnergySources.JBoxCode = '{Root Container.JBoxCode}'`  
Thanks,

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [July 29, 2011, 1:53am UTC](https://forum.inductiveautomation.com/t/query-help-distinct-rows/3140/2 "2011-07-29T01:53:49Z")

</div>

Sure just use the GROUP BY clause. Something like:`SELECT 
   LockOutPoints.LockOutPointID, 
   LockOutPoints.LockOutName, 
   LockOutPoints.LockOutPointLocation, 
   LockOutPoints.LockOutDevice
   LockOutPoints.LockOutImage
FROM LockOutEnergySources 
JOIN 
   LockOutPoints ON LockOutPoints.LockOutPointID = LockOutEnergySources.LockoutPoint
WHERE LockOutEnergySources.JBoxCode = '{Root Container.JBoxCode}' GROUP BY LockOutPoints.LockOutPointID`You may have to apply aggreagate functions like MAX, MIN, AVG or something for certain columns. Try it and if it errors out apply the functions.

---

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [July 29, 2011, 3:22pm UTC](https://forum.inductiveautomation.com/t/query-help-distinct-rows/3140/3 "2011-07-29T15:22:19Z")

</div>

I can get it to work without the image column but with it I get.

[quote]Operand data type image is invalid for (I tried them all) operator.  
[/quote]

I will look for another way to bind the data.

---

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [July 29, 2011, 8:14pm UTC](https://forum.inductiveautomation.com/t/query-help-distinct-rows/3140/4 "2011-07-29T20:14:51Z")

</div>

I decided to do this with a property change script. The first query returns all of the distinct Jboxes and I iterate through this list to retrieve the images. It may not be the cleanest but I can finish the job.

`if event.propertyName == "Data":
	strJBox = event.source.parent.JBoxCode
	loPoints = system.db.runPrepQuery("SELECT DISTINCT LockOutPoints.LockOutPointID, LockOutPoints.LockOutName, LockOutPoints.LockOutPointLocation, LockOutPoints.LockOutDevice FROM LockOutEnergySources JOIN LockOutPoints ON LockOutPoints.LockOutPointID = LockOutEnergySources.LockoutPoint WHERE LockOutEnergySources.JBoxCode = ?", [strJBox])
	headers = ["LockOutPointID","LockOutName","LockOutPointLocation","LockOutDevice","LockOutImage"]
	data = []
	for row in loPoints:
		LockOutID=row["LockOutPointID"]
		tempImage = system.db.runPrepQuery("SELECT LockOutPoints.blob FROM LockOutPoints WHERE LockOutPoints.LockOutPointID = ?", [LockOutID])
		LockOutImage = tempImage[0][0]
		data.append([LockOutID,row["LockOutName"],row["LockOutPointLocation"],row["LockOutDevice"],LockOutImage])
	event.source.Data2 = system.dataset.toDataSet(headers, data)`