2

is there any way how to sort the result set in GT.M by specific value?

Let's say I have global variable ^People(name,surname)=age and I want to get all the people with age between 20 and 40, ordered by their age?

JustUser
  • 91
  • 1
  • 7
  • 2
    create an index, ^PeopleAgeIndex(Age,name)=$i(previous value).... Does this work?? – Yogesh Nov 02 '13 at 02:29
  • 1
    Hi, thanks for your advice! It works perfect. But what if I want to get name and also surname of the person? Should I include into your index also surname? Is it correct solution? Isn't it then just the copy of the global variable and not its index? – JustUser Nov 02 '13 at 13:25
  • 2
    you can do as Yogesh suggested but include name and surname in the ^PeopleAgeIndex. Unfortunately this does duplicate the ^People global. Another alternative is to introduce an Age node in your ^People global like ^People(name,surname,age)="" this way you can $order through the global for all the names, and surnames where 20 <= age >=40 – igotmumps Nov 02 '13 at 21:59
  • 1
    You need not include surname or anything else in the Index global. This is how it will work.. You $o over the Index global and fetch the name out of it.. And then use the name and get the surname and whatever else you might want to get from your original global.. For example lets say I have ^People("XYZ","ABC")=20, ^People("XYZ1","ABC1")=19,,, My index will look like ^PeopleAgeIndex(20,"XYS")=1 and ^PeopleAgeIndex(19,"XYS1")=1... Now you can $o over ^PeopleAgeIndex to get XYS and then do $o(^People("XYS")) to get surname.. – Yogesh Nov 03 '13 at 04:16
  • Accept the answer, if it has satisfied your need. – Yogesh Dec 10 '13 at 13:50

1 Answers1

1

Edit: Sorry to Answer instead of continuing the comment chain... my permissions level isn't high enough to comment on this question yet.

As long as you won't have multiple people with the same name, but different surnames, Yogesh is correct. It would be best to use the index to find the correct node of ^People, then grab what you need from ^People. But if you would have multiple people with the same name, but a different surname, then you would want to include the surname in the ^PeopleAgeIndex subnodes.

FinrodFelagund
  • 207
  • 4
  • 13
  • 1
    I feel the best way to avoid bloating the index while still having proper indexes build is to use unquie ID like you would do in SQL tables, and use ID instead of name or anything else in the index. The first level of ^People global will be this unique ID. Easy way to get unique ID's would be as simple as $i() of the previous entry. – Yogesh Nov 07 '13 at 14:38
  • 1
    Agreed. Any time you're dealing with people, names are probably the worst way of indexing anything (unless you are sorting for display purposes). That being said, "Age" is probably fairly rough too. Date of Birth would typically be better, since that doesn't require any updates over time. – FinrodFelagund Nov 07 '13 at 14:40
  • 1
    I agree, storing integer equivalent of DOB (UTC seconds since epoch) will be much better. – Yogesh Nov 07 '13 at 16:22